SQL Server: How to perform an Update Statement with an Inner Join


The scenario is, you need to perform an update statement but you need to also inner join on to be able to add where criteria in to get the database records that you want. The command would look something like this:

UPDATE t1 SET UPDATED_FIELD = 0
FROM YOUR_FIRST_TABLE t1
INNER JOIN YOUR_SECOND_TABLE t2 On t1.ID = t2.ID
WHERE t1.FIELD1 = 'VALUE' AND t2.DATE = '2/27/2012'

The above joins two tables and then provides an additional filter in the where clause to get down to the records that you’re wanting to update. I’ve really only used something like this on SQL Server so I’m not sure how easily or if it ports to other flavors of SQL well.