CODEWITHSUNDEEP
sql-serverentity-frameworkt-sql
user94893
user94893

Reputation:

How to update all Id value in associated table(via foreign key) to another value?

This question might look a bit strange. But let me explain what is my real problem.

In my system, there is Building data that user can freely add data to the system. All of user data will be reviewed later. Sometime, user add building A as "Foo A". Sometime, they add it as "Foo bar A". Both of these record refer to the same real-world object.

For Admin, they need to review all input data and replace all invalid with the correct one. However, after user add new building. They also use this new building in other table like "Order" table.

enter image description here

I need some SQL script(SQL Server 2008 or later) to update all associated data with the new data. After that, I can safely remove invalid data from database.

Thanks,

PS.1 I have so many table like this. I cannot manually write some script for each table.

PS.2 Column name in associated table might not be "BuildingId" so we cannot use "BuildingId" as search condition to find associated table.

Upvotes: 1

Views: 1191

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6781

Assuming the dependent tables' field name will always contain BuildingID, you can use dynamic sql to build your update statements, then paste them into a new code window:

USE DatabaseName

DECLARE @OldBuildingID INT = 123
DECLARE @NewBuildingID INT = 456

select 'UPDATE DatabaseName.dbo.' + columns.TABLE_NAME + ' SET ' + columns.column_name +' = ' + CAST(@NewBuildingID AS VARCHAR(10))  + ' WHERE ' + columns.column_name + ' = ' + CAST(@OldBuildingID AS VARCHAR(10)) + ';' AS SQLDeleteStatement
from information_schema.COLUMNS columns
INNER JOIN sys.objects tables ON columns.TABLE_NAME = tables.name
where columns.COLUMN_NAME like '%BuildingID%'
AND tables.[type] = ( 'U' )

Upvotes: 3

Related Questions