Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as 'deleted' in a column in SQL.
So I am able to bring the account back, but there are certain triggers/constraints/something that is making it complicated to consolidate the data from the two accounts.
I cant bring back her 'child' records unless they have a link to the parent table, and I cannot have back both records in the parent table simultaneously because two staff numbers cannot exist at the same time (unless one is marked as deleted).
They deleted her new account, so I was able to bring the old one back. Now I need to add the absences that were put in for her new account to the restored old one, but if i try to do so by updating the ID column of her new absences to match the ID of the old (restored) user, I get the error
Msg 50000, Level 16, State 1, Procedure trsys_Absence, Line 83 [Batch Start Line 209] ------------------------------------------No link made with the Parent table.
So I thought perhaps I could update the record by somehow linking to the parent table via a join as such
UPDATE tbl_Absence SET b.ID = a.ID FROM tbl_Users a INNER JOIN tbl_Absence b ON a.ID = b.ID WHERE b.ID = 9999 AND b.deleted = '2019-11-14 10:00:00' AND a.ID = 9999;
But in this case
The multi-part identifier "b.ID" could not be bound.
Not sure if what I am doing will even work, but any suggestions of ways I could do this - and hat I am doing wrong in this particular case - would be great, thanks.