03 Apr 06 Update using an inner join (MSSQL)

Just searched the net trying to figure out how to do an update using an inner join. I figured it was possible, and I was right. Here’s how to do it:

UPDATE Table1
SET Table1.Title = Table2.Title
FROM Table1, Table2
where Table1.ID = Table2.ID

If you are using aliases – for instance if you are updating a table you are joining to itself, use this code:

UPDATE Table1Alias
SET Table1Alias.Title = Table2Alias.Title
FROM Table1 as Table1Alias, Table2 as Table2Alias
where Table1Alias.ID = Table2Alias.ID

Another scenario you may encounter is where you want to update a table, but use an inner join to further filter the rows you want to update.

Here’s an example of how to do this:

UPDATE Table1 Set Field1 = Value
FROM Table1 as t1
INNER JOIN Table2 as t2 on t1.Field2 = t2.Field2
WHERE Field3 = Value

That seems too simple, but it eluded me for a long time.

Learn more about your human rights at HumanRights.com