March 19, 2015 at 3:55 pm
I have a query, I am trying to update a certain column in my query you can see that is hard coded. The column that I am trying to update is "O_Test" I used a select statement trying to figure out how many records that accounts for with the entire database of all the tables. I got 643 records. So I am wondering if there is a way I can update all the columns without looking up each table and updating each one.
This Update statement wont work because I am accounting for all records in the DB of all tables associated of what I hard coded
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%O_Test%'
ORDER BY schema_name, table_name;
March 20, 2015 at 2:36 am
An update statement can only affect a single table. Hence to update multiple tables you need multiple update statements
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2015 at 4:12 am
Undocumented sp_MSforeachtable may process only tables needed
EXEC sp_MSforeachtable
@command1='select ''?'', count(ID) from ?',
@whereand='AND o.id in (select object_id from sys.columns c where c.name=''ID'')'
March 20, 2015 at 7:35 am
Yes, my original plan was basically use an update statement of each table but there about 30 tables it is listed in. So I wanted to find out was there a way where I can Update all columns that have the Hard coded value get updated from all the tables.
I know I can use this query listed below but that will take a immense amount of time. So I was thinking is there a way to set the values of within all the tables in the Database if possible
UPDATE table
SET column1 = expression1
WHERE table1.column = table2.column
March 20, 2015 at 8:10 am
An update statement can affect one and only one table. If you have to update values in 30 tables, you need 30 update statements. You can use something like foreachDB or use dynamic SQL to generate and execute the updates, but you will need to run one update per table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2015 at 8:27 am
Hmm, I guess I will do that then but Is there a way I can find all the values in my where clause that show me which table they are in? Because since they are in different tables I don't want to look in each table I think that value is in. From there I'll just use the Update query and do that manually
WHERE c.name LIKE '%O_Test%'
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply