syntax error in update with inner join query mysql

Question

My Query :

UPDATE i SET i.CurStock = i.CurStock-g.Qty  
    FROM inv_inventarymaster AS i INNER JOIN inv_goodsissue AS g  
    ON  i.ItemName = g.ItemName WHERE g.DATE='2014-03-20';

Error:

You have an error in your SQL syntax; check the manual that correspond to your MySQL server version for the right syntax to use near 'FROM inv_inventarymaster as i INNER JOIN inv_goodsissue as g ON ' at line 1

Kindly help me getting the right syntax.

Take a look here http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match/224740#224740 — Petra, Mar 20 '14 at 07:30

score 1 · Accepted Answer · answered Mar 20 '14 at 07:31

1

Try this ...Not Tested but as MYsql http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE inv_inventarymaster AS i INNER JOIN inv_goodsissue AS g SET 
i.CurStock = i.CurStock-g.Qty 
WHERE i.ItemName = g.ItemName and g.DATE='2014-03-20';

answered Mar 20 '14 at 07:31

naveen goyal

4,571
2
16
26

@user3092228 : dont forget to mark accepted if it helped...i am upvoting on your behalf!! ;) – NoobEditor Mar 20 '14 at 07:36

score 0 · Answer 2 · edited Mar 20 '14 at 07:34

0

its not a right thing to use FROM in Mysql UPDATE query.

You can use the query like this

UPDATE inv_inventarymaster AS i 
INNER JOIN inv_goodsissue AS g  ON i.ItemName = g.ItemName 
SET i.CurStock = i.CurStock-g.Qty  
WHERE g.DATE='2014-03-20';

edited Mar 20 '14 at 07:34

NoobEditor

15,563
19
81
112

answered Mar 20 '14 at 07:34

Mad Angle

2,347
1
15
33

syntax error in update with inner join query mysql

My Query :

Error:

2 Answers2