UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task.

Let us see the following example. We have two tables Table 1 and Table 2

Syntax:

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
WHERE t1.Col1 IN (21, 31)
GO

Real Example:

update dwdata.[dbo].[CLIENTFILES_5]
set dwdata.[dbo].[CLIENTFILES_5].ACCOUNTNUMBERS = t2.ACCOUNTNUMBER
from dwdata.[dbo].[CLIENTFILES_5] t1
inner join dwdataold.[dbo].[CLIENTFILES_5] t2
on t1.DWDOCID = t2.dwdocid