Query Syntax Error for UPDATE with INNER JOIN

Question

Hey i have an update query with an inner join but i cannot get the syntax correct to make it work... this is what i currently have:

UPDATE t1 
   SET t1.quantity = t1.quantity - ?  FROM items t1
INNER JOIN users t2 ON t1.id=t2.id
   WHERE t1.item_id=? AND t2.uid= ?

The syntax error says its near here:

near 'FROM items t1 INNER JOIN users t2 ON t1.id=

I'm using pdo encase you wondered why i have question marks!

Hope you can help!

Answer 1

Try following query, the syntax you have is valid in SQL Server but not in MySQL

UPDATE items t1
INNER JOIN users t2 ON t1.id=t2.id
SET t1.quantity = t1.quantity - ?  
WHERE t1.item_id=? AND t2.uid= ?

SQL DEMO