Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In my database, I have around 30 tables, each with an emp_number (INT) column.

How can I update the value of emp_number in all 30 tables?

share

migration rejected from stackoverflow.com Jul 2 '13 at 14:25

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Paul White, StanleyJohns, Nick Chammas, Kin, Mark Storey-Smith Jul 2 '13 at 14:25

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Do it via dynamic sql. But take care of order if you have relation defined on that column' –  Kamran Shahid Jul 1 '13 at 13:31
    
i feel compelled to say - oof normalization issue. you might take this opportunity to fix the schema. –  Randy Jul 1 '13 at 13:34
2  
so every single table in the DB has emp_number in it? are you sure? –  swasheck Jul 1 '13 at 17:12
2  
And you want to update every row, in every table, to some value? Does it matter what value is there now? Please provide more specifics. –  Aaron Bertrand Jul 1 '13 at 17:17

1 Answer 1

This uses sys.columns to only get the tables that have that column (even though you say they all do, I find it hard to believe that's true or that it will always be true). It also doesn't ignore schema (which can be an issue), or use undocumented, unsupported procedures to iterate through a cursor against each table.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  UPDATE ' + OBJECT_SCHEMA_NAME([object_id])
 + '.' + OBJECT_NAME([object_id])
 + ' SET emp_number = 42;'
FROM sys.columns
WHERE LOWER(name) = N'emp_number'
AND OBJECTPROPERTY([object_id], 'IsUserTable') = 1
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;

PRINT @sql;
-- EXEC sp_executesql @sql;

Now, you may not be expressing your requirement fully. Do you really want to set ALL rows in ALL tables to have the exact same emp_number? Why? Maybe what you meant is:

 + ' SET emp_number += 1;'

Or:

 + ' SET emp_number = CASE emp_number 
       WHEN 10 THEN 32
       WHEN 11 THEN 45
       -- etc. etc.
     END;'
share

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