[RESOLVED] Update a field in multiple tables

Hey all,
We have a database with a number of tables that have a field that needs updating. The field name is the same across all tables and the value we need to update to will also be the same across all tables.

I have the following statement that retrieves all of the tables with said field, but I'd like to know how I can update the fields as well...

Code:

---

SELECT      t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t  ON c.object_id = t.object_id
WHERE      c.name = 'UpdatePath'
ORDER BY    TableName

---

I would like to do something like this (which does not work)...

Code:

---

UPDATE TableName SET UpdatePath = 'update'
where TableName in (
        SELECT      t.name AS 'TableName'
        FROM        sys.columns c
        JOIN        sys.tables  t  ON c.object_id = t.object_id
        WHERE      c.name = 'UpdatePath'
        ORDER BY    TableName)

---

Does anyone have any idea about how to do this?
Thanks
Kevin

I haven't tried but I guess something can be done with dynamic SQL.
You essentially do a Loop on all the tables to get their names, construct the Dynamic SQL Query and have a change of the tablename on each iteration.
Unless something can be done with straight T-sql which I'm not aware of.

Something similar to this at example on the second answer without the alter table.
Well you need to count your tables to put it on the while variable but you get the idea.
https://stackoverflow.com/questions/...through-tables

Thanks sapator.
The examples introduce a bunch of new concepts to me, but hey, it's only code. I can probably make something work from it.

It is not that hard if you have basic SQL knowledge.
Just be careful with the creation of the string as sometimes it's tricky.

Hello,@kebo
Please try this query, To Update a field in multiple tables

Code:

---

Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
From INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'CreatedDateTime'

---

I hope this query will be useful to you.
Thank you.

Quote:

---

Originally Posted by Sherin

Hello,@kebo
Please try this query, To Update a field in multiple tables

Code:

---

Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
From INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'CreatedDateTime'

---

I hope this query will be useful to you.
Thank you.

---

Thank for that Sherin, but your sql will only return a table with all of the update commands (one record for each table).

How do I now get the server to process each record and actually perform the update?

Try this - in a query window or a stored procedure if you need to use it regularly

Code:

---

Declare @SQLStatements Table (SqlText nvarchar(max))
Declare @SQLText nvarchar(max)

Insert into @SQLStatements Select 'Update'...and the rest of Sherin's select...

While Exists(Select * From @SQLStatements)
Begin
    Set @SQLText=(Select Min(SQLText) From @SQLStatements)
    Exec (@SQLText)
    Delete From @SQLStatements Where SQLText=@SQLText
End

---

Why not save list of the queries in separate table? When you add or remove table with the field to be updated, then updated your queries in the "queries" table. In your application you can cache the queries and just execute without messing with system tables each time.

If there is requirement to create new "table with field to update" each few seconds or minutes, then it is different story and pre-caching will not work well, but then there is something wrong in the application and database design.

Quote:

---

Originally Posted by szlamany

Try this - in a query window or a stored procedure if you need to use it regularly

---

Thank you. It worked great. For anyone else that needs it, the complete sql looks like this...

Code:

---

Declare @SQLStatements Table (SqlText nvarchar(max))
Declare @SQLText nvarchar(max)

Insert into @SQLStatements Select 'UPDATE ' + TABLE_NAME + ' SET FIELD_TO_UPDATE= ''NEW_VALUE'' '
                          From INFORMATION_SCHEMA.COLUMNS
                          WHERE COLUMN_NAME = 'FIELD_TO_UPDATE'

While Exists(Select * From @SQLStatements)
Begin
    Set @SQLText=(Select Min(SQLText) From @SQLStatements)
    Exec (@SQLText)
    Delete From @SQLStatements Where SQLText=@SQLText
End

---