CODEWITHSUNDEEP
mysql
user3092228
user3092228

Reputation: 49

syntax error in update with inner join query mysql

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.

Upvotes: 1

Views: 515

Answers (2)

Mad Angle
Mad Angle

Reputation: 2330

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';

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

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';

Upvotes: 1

Related Questions