Update column value of mutiple tables, only 1 row affected

Question

Here is the link to the solution I found and used The code provided in this question should perfectly solve my case.

however, there is only 1 row affected. I wonder if that means I should add a loop or other conditions to make it run through all the tables.

    Declare @ColName as nVarchar(100), @NewValue as nVarchar(50) 
    Set @ColName = 'test_column'  -- 'your col name'
    Set @NewValue = getDate() -- your date time value
    Select 'Update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ''' + @NewValue + '''' From INFORMATION_SCHEMA.COLUMNS Where column_name = 'test_column'

You need to add your code to the question. Don't just point us to a link and ask us to guess. — Yatrix, Jul 10 '15 at 03:03
Thank you for your advice. I just updated the code. Hope it will give ease to anyone see that. — Dilabeing, Jul 10 '15 at 03:08

score 1 · Answer 1 · answered Jul 10 '15 at 03:04

You can update only 1 table at a time. You can update multiple rows in the same table or multiple columns, but not multiple tables in a single update. You would need a loop to iterate for each table of the update.

Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' ' AS SqlStatement INTO #T
From INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CreatedDateTime'

WHILE EXISTS (SELECT * FROM #T) BEGIN
    DECLARE @Sql VARCHAR(4000) = (SELECT TOP 1 SqlStatement FROM #T)
    EXEC (@Sql)
    DELETE #T WHERE SqlStatement = @Sql
END

Please feel free to upvote an answer if it helped you and accept it if it solved your question. This helps others to know which answers may have helped with your question. It also increases your reputation. — Jason W, Jul 14 '15 at 02:43

Update column value of mutiple tables, only 1 row affected

1 Answers1