Update Table in MySQL
I had problem on updating a field of one table which had link up to another table. The structures are like these:
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.
SET model_no = 'xx'
From item_detail, purchase_item
Where item_detail.item_ID = purchase_item.item_ID
AND po_no = 'x'
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 !!
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
update value in temp table - this will update all rows in temp table
CREATE TEMPORARY TABLE temp
FROM Item_Detail, Purchase_Item
WHERE Item_Detail.Item_ID = Purchase_Item.Item_ID
AND Purchase_Item.PO_No = 'X'
use replace to replace rows in Item_Detail table with rows from temp table where Item_ID matches.
UPDATE temp SET Model_No='xx';
**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
SELECT * FROM temp;
it works...but surely theres a better way?
and as always test first:)
hope this helps