CODEWITHSUNDEEP
sqlsql-serverupdates
Baganaakh
Baganaakh

Reputation: 67

Update all occurence of a specific column in entire database

I have Asset table and assetid(column) in many table use

I want to update all occurences of tables in all occurences of inserts in database

assetid(column) WHERE value has 878 to 1 FROM all tables in database

I know normal update

update AdminInterests set assetid = 1 where 878

I just want to how to apply update through entire database in all tables

Upvotes: 1

Views: 678

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You could use this query to create all the needed UPDATE statements.

SELECT 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET assetid  = 1 where asssetid = 878;'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='assetid'

And then you can execute those UPDATE statements.

Sample Demo Here

Upvotes: 2

Matt Vickery
Matt Vickery

Reputation: 121

One potential solution is that you could read database meta data tables and build a dynamic query to find all the columns/attributes you’d like to update and then run an update against them. It’s quite a lot of work to do that but does provide a solution. It’s also a programmatic solution rather than one that you can do with a query.

Upvotes: 1

Related Questions