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

,

2 Responses to UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

  1. Spencer 04/07/2017 at 7:41 pm #

    Hi Juan, Based on your update statement, it looks like you are updating Docuware. Are you able to do backend updates via SQL? We have been using the front end but it takes so long to index.

  2. Juan 07/27/2017 at 12:18 pm #

    Yes, this will be ran in the back end.

Leave a Reply

*