Sql tip #2 – Update table using a join

Occasionally I’ve had to update a database table with a value from another database table.

Now I’m not talking about updating a single value in a single row, that wouldn’t be hard with an update statement, we’re talking billions and trillions of rows… realistically though hundreds.

So we have two or more tables that we can join in a sql query.
Let’s say we have a tables TableA and TableB and that TableB has a foreign key referencing TableA.
A select query joining those two would look something like this:

SELECT
 A.ShippingDate,
 B.ShippingDate -- just so B won't be left out
FROM
 TableA A
 INNER JOIN TableB B
  ON A.Key = B.ForeignKey

That was easy. A simple inner join and we have all the columns we need.

An update through a join follows pretty much the same rules in the join department but replaces the SELECT with an UPDATE.
For the sake of the demo let’s now say TableA and TableB have the “same” column (as seen above) and that for every row in one table (TableA) we need to update the matching rows in the other (TableB).
For the sake of the demo let’s assume this value is a shipping date when an order is sent to a customer. We’ve set the value on TableA and now we need to mark all referenced rows in TableB with the same value, taken from TableA.

This is how it would be achieved:

UPDATE B
 SET B.ShippingDate = A.ShippingDate
FROM 
 TableA AS A
 INNER JOIN TableB AS B
  ON A.Key = B.ForeignKey

As you can see this SQL statement is using the same join as the first SQL statement, the only difference is the Update syntax on top.

So… like so many other problems this is not that tricky once you know how to do it.

And now you know 😉

Enjoy