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
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.
Yes, this will be ran in the back end.