Update Table in MySQL

[littleblack]

I had problem on updating a field of one table which had link up to another table. The structures are like these:

Table Item_Detail
Item_ID
Model_No

Table Purchase_Item
PO_No
Item_ID

UPDATE Item_Detail SET Model_No = 'xx'
FROM Purchase_Item WHERE
Item_Detail.Item_ID = Purchase_Item.Item_ID
AND PO_No = 'X'

When I try to update using the above SQL, an error eccour.

Moreover, my DB is build on MySQL rather than SQL Server, is MySQL not support INNER JOIN command ?
I had already try the following 2 different SQL command but there is a error msg about syntax error at line 3.

Update item_detail
SET model_no = 'xx'
From item_detail, purchase_item
Where item_detail.item_ID = purchase_item.item_ID
AND po_no = 'x'

UPDATE Item_Detail
SET Model_No = 'xx'
FROM Purchase_Item JOIN Item_Detail ON Item_Detail.Item_ID = Purchase_Item.Item_ID
AND PO_No = 'X'

Pls help !!

[martm]

AFAIK, mysql does not support update (or delete) queries using joins.
Apparently this is on the todo list for version 4.x

Here is a possible workaround (worked for me using mysql 3.23.49):

create a temp table to get rows you want to update from Item_Detail table
i.e. rows that match your select criteria

CREATE TEMPORARY TABLE temp
SELECT Item_Detail.*
FROM Item_Detail, Purchase_Item 
WHERE Item_Detail.Item_ID = Purchase_Item.Item_ID
AND Purchase_Item.PO_No = 'X'

update value in temp table - this will update all rows in temp table

UPDATE temp SET Model_No='xx';

use replace to replace rows in Item_Detail table with rows from temp table where Item_ID matches.
**this assumes each Item_ID has unique value**
if this is not unique the rows from temp will be added to your Item_Detail table

REPLACE Item_Detail
SELECT * FROM temp;

it works...but surely theres a better way?
and as always test first

hope this helps