0

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.

  • are you sure there is a column named "ID" on table tbl_Absence? – Mehmet Nov 14 at 13:29
  • yes i am sure... – jack Nov 14 at 13:41
0

Change your statement to

UPDATE b
   SET b.ID = a.ID
  FROM tbl_Users        AS a
 INNER JOIN tbl_Absence AS b
    ON a.ID = b.ID
 WHERE b.ID      = 9999
   AND b.deleted = '2019-11-14 10:00:00'
   AND a.ID      = 9999;

(just the first line is changed - use the alias b (for tbl_absence) direct after the UPDATE

PS: the AND a.ID = 9999 is obsolete too, since is implied by the JOIN condition

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.