-
Dec 22nd, 2020, 11:50 AM
#1
[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
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Dec 22nd, 2020, 12:03 PM
#2
Re: Update a field in multiple tables
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 22nd, 2020, 12:37 PM
#3
Re: Update a field in multiple tables
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 22nd, 2020, 12:50 PM
#4
Re: Update a field in multiple 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.
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Dec 22nd, 2020, 01:04 PM
#5
Re: Update a field in multiple tables
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.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 23rd, 2020, 10:58 PM
#6
Member
Re: Update a field in multiple tables
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.
< advertising removed by moderator >
-
Dec 24th, 2020, 08:56 AM
#7
Re: Update a field in multiple tables
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?
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Dec 24th, 2020, 09:04 AM
#8
Re: Update a field in multiple tables
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
-
Dec 24th, 2020, 09:20 AM
#9
Re: Update a field in multiple tables
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.
-
Dec 24th, 2020, 09:20 AM
#10
Re: Update a field in multiple tables
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
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|