UPDATE linking other tables

[don2241]

Hi!

I am trying to do a simple update function in mySql.
NOTE. I am using version 3.0.9.4-beta

I have 2 tables:
Items and Sales_details

I want to update a field called >Sales_value< in Sales_details table using value from Items called >Sell< times (*) >Sales_qty< from Sales_details table.

This is the codes I have been trying:

UPDATE Sales_details inner join Items on Sales_details.fk_Item_ID = Items.Item_ID
SET Sales_details.Sales_value = (Sales_details.Sales_qty * Items.Sell)

Error msg:[development_DB] ERROR 1064: You have an error in your SQL syntax near 'inner join Items on Sales_details.fk_Item_ID = Items.Item_ID
SET Sales_details.S' at line 1

and

UPDATE Sales_details
SET Sales_details.Sales_value = (select (Sales_details.Sales_qty * Items.Sell)
from Items inner join Sales_details on Items.Item_ID = Sales_details.fk_Item_ID)
where Sales_details.fk_Item_ID = (select Item_ID from Items)

Error msg:[development_DB] ERROR 1064: You have an error in your SQL syntax near 'select (Sales_details.Sales_qty * Items.Sell)
from Items inner join Sales_detai' at line 2

Thank you for any help
/Martin

 

[ericbrunson]

Version 3.x of mysql cannot do nested selects.