CODEWITHSUNDEEP
sql-servert-sql
Kenoyer130
Kenoyer130

Reputation: 7318

Update Foreign key table reference after bulk delete/insert?

I have table A containing parent/child records. I have table B that references Table A. The foreign key is not enforced. I am doing a bulk delete/insert to update the child records to point to the correct parent. This will assign the child records new primary keys which I need to also update in table B.

Is there any way to do this without using a cursor or row by row processing?

Upvotes: 0

Views: 293

Answers (1)

mccee
mccee

Reputation: 1667

Could you post some sample data and how it should be corrected? Without seeing the actual table schema, I can only makes some assumptions, so this may not work perfectly:

select PrimaryKeyField AS RowID, ChildKeyField AS OldChildID, newid() AS NewChildID
into #UpdateTable
from TableA
where SomeConditionToSelectTheChildRecords

update TableA
set TableA.ChildKeyField = #UpdateTable.NewChildID
from TableA
    inner join #UpdateTable on TableA.PrimaryKeyField = #UpdateTable.RowID

update TableB
set TableB.ChildKeyField = #UpdateTable.NewChildID
from TableB
    inner join #UpdateTable on TableB.ChildKeyField = #UpdateTable.OldChildID

Upvotes: 1

Related Questions