update by joining tables

You want to update one table with values from another (make sure you want to update and not insert). Each row corresponds to one row in another table, so a join needs to be used. Use 'from' with the update statement.

UPDATE TableToBeModified
SET fieldA = t.fieldA
FROM TableToBeJoined t
WHERE TableToBeModified.fieldB = t.fieldB

There are several gotchas for this:

• TableToBeModified cannot be aliased, so use its full name
• only one table can be updated at a time, so you can't say “set t.fieldC = true” in the query above