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 stackoverflow.com/questions/224732/…

Petra
– Petra

2014-03-20 07:30:36 +00:00
Commented Mar 20, 2014 at 7:30 — Petra
– Petra, Commented Mar 20, 2014 at 7:30

naveen goyal · Accepted Answer · 2014-03-20 07:31:50Z

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, 2014 at 7:31

naveen goyal

4,6272 gold badges18 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NoobEditor Over a year ago

@user3092228 : dont forget to mark accepted if it helped...i am upvoting on your behalf!! ;)

NoobEditor · Accepted Answer · 2014-03-20 07:34:59Z

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, 2014 at 7:34

NoobEditor

16k20 gold badges85 silver badges117 bronze badges

answered Mar 20, 2014 at 7:34

Mad Angle

2,3301 gold badge18 silver badges33 bronze badges

Collectives™ on Stack Overflow

syntax error in update with inner join query mysql

My Query :

Error:

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

My Query :

Error:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related