Update a table using JOIN in SQL Server?
I want to update a column in a table making a join on other table e.g.:
UPDATE table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] SET a.CalculatedColumn= b.[Calculated Column] WHERE b.[common field]= a.commonfield AND a.BatchNO = '110'
But it is complaining :
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.
What is wrong here?
You don't quite have SQL Server's proprietary
UPDATE FROM syntax down. Also not sure why you needed to join on the
CommonField and also filter on it afterward. Try this:
UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = '110';
If you're doing something really silly - like constantly trying to set the value of one column to the aggregate of another column (which violates the principle of avoiding storing redundant data), you can use a CTE (common table expression) - see here and here for more details:
;WITH t2 AS ( SELECT [key], CalculatedColumn = SUM(some_column) FROM dbo.table2 GROUP BY [key] ) UPDATE t1 SET t1.CalculatedColumn = t2.CalculatedColumn FROM dbo.table1 AS t1 INNER JOIN t2 ON t1.[key] = t2.[key];
The reason this is really silly, is that you're going to have to re-run this entire update every single time any row in
table2 changes. A
SUM is something you can always calculate at runtime and, in doing so, never have to worry that the result is stale.
Read more… Read less…
Try it like this:
begin tran UPDATE a SET a.CalculatedColumn= b.[Calculated Column] FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field] WHERE a.BatchNO = '110' commit tran
Answer given above by Aaron is perfect:
UPDATE a SET a.CalculatedColumn = b.[Calculated Column] FROM Table1 AS a INNER JOIN Table2 AS b ON a.CommonField = b.[Common Field] WHERE a.BatchNo = '110';
Just want to add why this problem occurs in SQL Server when we try to use alias of a table while updating that table, below mention syntax will always give error:
update tableName t set t.name = 'books new' where t.id = 1
case can be any if you are updating a single table or updating while using join.
Although above query will work fine in PL/SQL but not in SQL Server.
Correct way to update a table while using table alias in SQL Server is:
update t set t.name = 'books new' from tableName t where t.id = 1
Hope it will help everybody why error came here.
MERGE table1 T USING table2 S ON T.CommonField = S."Common Field" AND T.BatchNo = '110' WHEN MATCHED THEN UPDATE SET CalculatedColumn = S."Calculated Column";
Seems like SQL Server 2012 can handle the old update syntax of Teradata too:
UPDATE a SET a.CalculatedColumn= b.[Calculated Column] FROM table1 a, table2 b WHERE b.[common field]= a.commonfield AND a.BatchNO = '110'
If I remember correctly, 2008R2 was giving error when I tried similar query.
I find it useful to turn an UPDATE into a SELECT to get the rows I want to update as a test before updating. If I can select the exact rows I want, I can update just those rows I want to update.
DECLARE @expense_report_id AS INT SET @expense_report_id = 1027 --UPDATE expense_report_detail_distribution --SET service_bill_id = 9 SELECT * FROM expense_report_detail_distribution erdd INNER JOIN expense_report_detail erd INNER JOIN expense_report er ON er.expense_report_id = erd.expense_report_id ON erdd.expense_report_detail_id = erd.expense_report_detail_id WHERE er.expense_report_id = @expense_report_id