Updating a table using a join in SQL Server (UPDATE FROM)

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
Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Database, SQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s