Incorrect syntax error for Update-Inner Join statement

Question

I'm using Microsoft SQL Server and when writing this code I am getting an incorrect syntax error for the "Inner" and also "=" within the SET line. I've looked into this and think everything is set up correctly, can someone point out why if possible?

USE [db1]

GO

UPDATE a.[table1] 
INNER JOIN a.[table1] = a.[table2]
ON a.[table1].[field1] = a.[table2].[field1]
SET [field2] = a.[table2].[field2]
WHERE [ID] IN (SELECT #TempTable.[ID] FROM #TempTable)

I doubt you searched very long. First result: [SQL update query using joins](http://stackoverflow.com/questions/982919/sql-update-query-using-joins) — HoneyBadger, Feb 08 '17 at 12:55

score 2 · Accepted Answer · answered Feb 08 '17 at 12:55

Well, I have absolutely no idea where you got the syntax for an UPDATE with a JOIN from, but it's pretty messed up. This should be the right code:

UPDATE t1
SET t1.field2 = t2.field2
FROM dbo.table1 t1
INNER JOIN dbo.table2 t2
    ON t1.field1 = t2.field2
WHERE [ID] IN (SELECT [ID] FROM #TempTable);

score 0 · Answer 2 · answered Feb 08 '17 at 12:55

0

UPDATE  a
SET [field2] = a.[table2].[field2]
from  a.[table1] 
INNER JOIN a.[table1] = a.[table2]
ON a.[table1].[field1] = a.[table2].[field1]
WHERE [ID] IN (SELECT #TempTable.[ID] FROM #TempTable)

See if it is working

answered Feb 08 '17 at 12:55

Ayush Kalia

31
1
7

score 0 · Answer 3 · answered Feb 08 '17 at 12:58

0

try following

UPDATE a.[table1] 
SET [field2] = a.[table2].[field2]
From a.[table1]
INNER JOIN a.[table2]
ON a.[table1].[field1] = a.[table2].[field1]
WHERE [ID] IN (SELECT #TempTable.[ID] FROM #TempTable)

answered Feb 08 '17 at 12:58

jjj

1,136
3
18
28

score 0 · Answer 4 · answered Feb 08 '17 at 13:09

0

Update using inner join

UPDATE T1
    SET T1.[field2] = T2.[field2]
    FROM a.[table1] AS T1 
    INNER JOIN a.[table2] AS T2
    ON T1.[field1] = T2.[field1]

answered Feb 08 '17 at 13:09

PowerStar

893
5
15

Incorrect syntax error for Update-Inner Join statement

4 Answers4