How do I dynamically set the table and field name in an update query?

Question

I'd like to loop over a list of tables. For each table, I'd like to run an update query.

Psuedo code:

ArrayOfTablesObjects = {['tablename1','fieldname1'],['tablename2','fieldname2']......}

foreach @tablename in ArrayOfTablesObjects 

UPDATE @tablename
SET  @fieldname = 'xyz'
WHERE @fieldname = '123'

end foreach

Answer 1

You need to use dynamic SQL for this. The EXEC function will execute an ad-hoc sql statement passed in as a string.

DECLARE @UpdateSql AS varchar(2000)

foreach @tablename in ArrayOfTablesObjects 

SET @UpdateSql = 'UPDATE ' + @tablename + ' SET ' + @fieldname ' + = ''xyz'' WHERE ' + @fieldname + ' = ''123'''
EXEC (@UpdateSql)

end foreach

Answer 2

Does this help?

Answer 3

This can only be done using dynamic SQL. This is a very dangerous techique to use and should never be done without a great deal of thought as to how it could harm the database and how you can write it more carefully to protect the database. Please read this article before you consider writing dynamic SQL. http://www.sommarskog.se/dynamic_sql.html

Answer 4

Thanks Harpo for your answer. I've used that idea to build the following sql statement. We have many tables 50+ that all have the same type of data (employee id) but that field name might be different. So, depending on the table name the field to be updated will be different. My actual WHERE and SET statement are more complicated than this example, but thats not important to this problem.

I first create a temporary table to store the table/fields I want to update.

I then loop over these records, and generate the SQL. For those of you who don't like, dynamic sql, you can just use a print statement and then copy paste that into another query window and execute it. Or you can call the EXEC statement.

I'd like to accept your answers, but then didn't quite answer my question, partly because I didn't explain myself fully. Either way, thanks for your help.

DECLARE @TableFieldDictionary TABLE
(
    tablename VARCHAR(100),
    fieldname varchar(100)
)

insert into @TableFieldDictionary(tablename,fieldname) values ('table1','field1');
insert into @TableFieldDictionary(tablename,fieldname) values ('table2','field2');
--put more insert statements here.  In my case, I have 50 inserts


declare cursor_dictionary cursor
for select tablename, fieldname from @TableFieldDictionary

open cursor_dictionary

declare @looptablename VARCHAR(100)
declare @loopfieldname varchar(100)

fetch next from cursor_dictionary
into @looptablename,@loopfieldname

DECLARE @UpdateSql AS varchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN   
    SET @UpdateSql = 'UPDATE ' + @looptablename + 
                     ' SET ' + @loopfieldname + ' = 123' +
       ' WHERE ' + @loopfieldname + ' = 456'

    print @updatesql
    --EXEC(@updatesql)

    fetch next from cursor_dictionary
    into @looptablename,@loopfieldname
END 

CLOSE cursor_dictionary
DEALLOCATE cursor_dictionary