0

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'

  • At a first glance, this sounds like a design issue, can't you create a table that holds the IsDeleted value, and link your other tables there? That way you'd only need to change it on spot – Yannick Liekens Nov 12 '19 at 12:57
  • Unfortunately I can't change anything about the app - I barely know how it works myself, it's not my domain - just now that they need help. – jack Nov 12 '19 at 13:02
  • Get a list of the tables, and then use a good text editor to do multi-line pastes of the rest of the DELETE statement. – Tony Hinkle Nov 12 '19 at 13:07
  • 2
    You can use a cursor with the INFORMATION_SCHEMA query you referenced, providing all the tables you want to apply the UPDATE statement to as a result set, using it in a loop generating dynamic UPDATE SQL statements for sp_execute calls. You'll have to inspect the value of the ID your querying for to guard against SQL injection. – MattyZDBA Nov 12 '19 at 13:34
  • 2
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.