UPDATE JOIN query help

[jamesloi]

Hi,

I am trying to use this update query

UPDATE CD_data 
SET stock_level=stock_level-5 
FROM CD_data  

INNER JOIN cart 
ON CD_data.Item_code=cart.itemId

This is causing a mysql syntax error at 'FROM... Does anyone know how i can fix the syntax of this query.....?

Thanks

[Drakla]

I just had a look at the MySQL manual, and him say:

1.8.3 Things That Must be Done in the Near Future

Atomic multi-table updates:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

So for now you're going to have to use a loop. It also says that subqueries will be available in version 4.1... when

UPDATE CD_info SET stock_level=stock_level-5 WHERE CD_id IN (SELECT product_ID FROM cart)

Will be the answer.