Sql-server – Update 1 record across 135 tables

dmlsql server

There is an application, it maintains delete flag column i.e. if a delete happen the column marked as 1 (in the deleted column).

To retrieve the record I must take this 1 to a NULL

There are 135 tables having the deleted column, and all of them must be updated based on ID column (when it has particular value)

( SELECT *
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted')

How can I perform: UPDATE all tables containing the deleted column SET deleted = NULL WHERE ID = 'XXXX'

Best Answer

If this is a manual update you could script out the commands you need:

SELECT 'UPDATE '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +
' SET deleted = NULL 
WHERE ID =1;'
FROM   INFORMATION_SCHEMA.columns
WHERE  COLUMN_NAME = 'deleted';

But, be wary of these INFORMATION_SCHEMA dmvs.

Aaron Bertrand lists some good reasons not to use them in this blogpost on bad habits:

... I feel similarly about the INFORMATION_SCHEMA views. Since they are incomplete, aren't being updated for most new features, and Microsoft themselves recommend against them, I find it hard to justify using them in cases where you can (like getting just a list of columns from a table), but then having to use the catalog views when you need information the INFORMATION_SCHEMA views simply don't have (like partitions, or filtered indexes, or INCLUDE columns). Unless you need to write code that will work on multiple RDBMS platforms (in which case you can't use any of the SQL Server-specific features anyway), why not just use the catalog views always?...

So here is another solution using sys.tables & sys.columns

SELECT 'UPDATE '+ QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) +
' SET deleted = NULL 
WHERE ID =1;'
FROM   sys.tables t
WHERE EXISTS 
(
SELECT * FROM 
sys.columns c
WHERE t.object_id = c.object_id 
AND  c.[name] = 'deleted'
);

Testing

CREATE TABLE dbo.tableNR1(Id int, deleted bit);
INSERT INTO dbo.tableNR1(id,deleted)
VALUES(1,1);

CREATE TABLE dbo.tableNR135(Id int, deleted bit);
INSERT INTO dbo.tableNR135(id,deleted)
VALUES(1,1);

Result for both:

UPDATE [dbo].[tableNR1] SET deleted = NULL   WHERE ID =1;
UPDATE [dbo].[tableNR135] SET deleted = NULL   WHERE ID =1;

DB<>Fiddle

Related Question