Update a table using JOIN in SQL Server?
Update a table using JOIN in SQL Server?
Question
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?
Accepted Answer
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