Yes, this is most probably trivial to everybody except me. For whatever reason, I simply don’t remember this syntax. Every time I have to do this, I Google it and usually end up on this solution. You can go there if you like – I’ve added nothing of value other than putting it in terms of the structure of the tables I’m working with (although I’ve changed field and table names). This way, if I read this post myself a few times, maybe I’ll remember this shit.
Let’s say you have an Widget table and a Customer table, where Widget has a CustomerID foreign key. Both have a foreign key to a Contact table, which was recently added. So I changed the code that generates (inserts) widgets to always use the configured contact on the customer, if there is one as it is a nullable foreign key. (i.e. FK added using WITH NOCHECK ADD CONSTRAINT.) That’s great, but now I need to update the existing widgets to have the contact ID as well.
(The business rule I have is simply that widgets must have the default customer contact when created. If you change the contact on a customer, it does not get applied retroactively on widgets. However, after adding these new fields, I do need to fix the existing widgets. I could leave them null but it makes sense to update all of them to use the correct customer contact as set up today.)
The solution is to use the UPDATE FROM syntax, which I so happen to always forget. I don’t know why that is. Maybe it’s old age approaching; maybe it is the price I pay for my years of drug abuse. (I’m four years clean at the time of writing this, by the way. Meth is a helluva drug.) I don’t know, but it seems that my brain cells dedicated to remembering SQL are unreliable. It’s easy though, and for the tables I have described, the update query would look like so:
UPDATE w SET w.ContactID = c.ContactID FROM Widget w JOIN Customer c ON c.ID = w.CustomerID WHERE w.ContactID IS NULL AND c.ContactID IS NOT NULL