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.
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.
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
UPDATE mytable
SET myfield = CASE other_field
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
From mytable
Join otherTable on otherTable.id = mytable.id
Where othertable.somecolumn = '1234'
More alternatives here.
Another approach would be to use MERGE
;WITH cteTable1(CalculatedColumn, CommonField)
AS
(
select CalculatedColumn, CommonField from Table1 Where BatchNo = '110'
)
MERGE cteTable1 AS target
USING (select "Calculated Column", "Common Field" FROM dbo.Table2) AS source ("Calculated Column", "Common Field")
ON (target.CommonField = source."Common Field")
WHEN MATCHED THEN
UPDATE SET target.CalculatedColumn = source."Calculated Column";
-Merge is part of the SQL Standard
-Also I'm pretty sure inner join updates are non deterministic.. Similar question here where the answer talks about that http://ask.sqlservercentral.com/questions/19089/updating-two-tables-using-single-query.html
I had the same issue.. and you don't need to add a physical column.. cuz now you will have to maintain it.. what you can do is add a generic column in the select query:
EX:
select tb1.col1, tb1.col2, tb1.col3 ,
(
select 'Match' from table2 as tbl2
where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2
)
from myTable as tbl1
Aaron's approach above worked perfectly for me. My update statement was slightly different because I needed to join based on two fields concatenated in one table to match a field in another table.
--update clients table cell field from custom table containing mobile numbers
update clients
set cell = m.Phone
from clients as c
inner join [dbo].[COSStaffMobileNumbers] as m
on c.Last_Name + c.First_Name = m.Name
