update using INNER JOIN

Question

What is going wrong in this SQL statement , i can't find anything Wrong

UPDATE Master_Adjust 
    INNER JOIN [dbo].[Master] ON Master_Adjust.Empno = [dbo].[Master].Empno  
                         SET Master_Adjust.GratuityYtodate = [dbo].[Master].GratuityYtodate,  
                             Master_Adjust.ManualBasicPay = 0, 
                             Master_Adjust.ManualTax = 0,
                             Master_Adjust.ManualNapsa = 0,
                             Master_Adjust.ManualPension = 0,
                             ManualCharity = 0

ERROR Message is

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'INNER'.

possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Martin Smith, May 14 '13 at 14:28
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Aaron Bertrand, May 14 '13 at 14:36

Abe Miessler · Accepted Answer · 2013-05-14T14:37:42.477

7

Looks all wrong. Try this:

 UPDATE ma
 SET ma.GratuityYtodate = [dbo].[Master].GratuityYtodate,  
     ma.ManualBasicPay = 0, 
     ma.ManualTax = 0,
     ma.ManualNapsa = 0,
     ma.ManualPension = 0,
     ManualCharity = 0 
 FROM Master_Adjust as ma
 INNER JOIN [dbo].[Master] ON ma.Empno = [dbo].[Master].Empno

edited May 14 '13 at 14:37

answered May 14 '13 at 14:29

Abe Miessler

82,532
99
305
486

`error msg is Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Master_Adjust.GratuityYtodate" could not be bound.` – Abhishek May 14 '13 at 14:36

HLGEM · Answer 2 · 2013-05-14T15:05:24.863

1

That is not t-sql syntax. Try:

UPDATE ma

                         SET ma.GratuityYtodate = M.GratuityYtodate,  
                             ma.ManualBasicPay = 0, 
                             ma.ManualTax = 0,
                             ma.ManualNapsa = 0,
                             ma.ManualPension = 0,
                             ma.ManualCharity = 0 
from  Master_Adjust ma
INNER JOIN [dbo].[Master] m ON ma.Empno = M.Empno

edited May 14 '13 at 15:05

answered May 14 '13 at 14:28

HLGEM

94,695
15
113
186

Hi @HLGEM error msg is `Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Master_Adjust.GratuityYtodate" could not be bound.` – Abhishek May 14 '13 at 14:32
You could also simply remove the aliases from the set statement, it is assumed they are from the table you are updating. – HLGEM May 14 '13 at 15:06

score 0 · Answer 3 · answered May 15 '13 at 15:00

Try the following query.

UPDATE [dbo].Master_Adjust 
SET 
   Master_Adjust.GratuityYtodate = [dbo].[Master].GratuityYtodate
  ,Master_Adjust.ManualBasicPay = 0
  ,Master_Adjust.ManualTax = 0
  ,Master_Adjust.ManualNapsa = 0
  ,Master_Adjust.ManualPension = 0
  ,ManualCharity = 0 
 from
    [dbo].[Master]
INNER JOIN
    [dbo].Master_Adjust 
ON
    [dbo].Master_Adjust.Empno = [dbo].[Master].Empno

update using INNER JOIN

3 Answers3