MYSQL Update statement - unknown column
I have a select query which works correctly with the below output:
I would like to create an update statement to set the value of the despgoods_alldetails.loadid column = loaddetails.loadid where despgoods_alld开发者_Go百科etails_despgoodsid=loaddetails.despgoodsid.loadid.
I was thinking along the lines of
update despgoods_alldetails set despgoods_alldetails.loadid = loaddetails.loadid where despgoods_alldetails.despgoodsid=loaddetails.despgoodsid
This query fails with the below error:
any idea how I can get this update statement to work?
Thanks, Ryan
Almost had it, you just need a reference to the second table.
UPDATE despgoods_alldetails, loaddetails
SET despgoods_alldetails.loadid = loaddetails.loadid
WHERE despgoods_alldetails.despgoodsid=loaddetails.despgoodsid
loaddetails
is a separate table from the one you are trying to update. This can't be done directly in a SQL update. You'll need a subselect like this instead:
update despgoods_alldetails
set despgoods_alldetails.loadid = (
select loaddetails.loadid
from loaddetails
where despgoods_alldetails.despgoodsid=loaddetails.despgoodsid
)
You need to put two tables after update clause like this
update despgoods_alldetails, loaddetails set despgoods_alldetails.loadid = loaddetails.loadid where despgoods_alldetails.despgoodsid=loaddetails.despgoodsid
Can you found one simple example in this page http://dev.mysql.com/doc/refman/5.0/en/update.html
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
update
despgoods_alldetails as da
join
loaddetails as l
using
(despgoodsid)
set
da.loadid = l.loadid;
精彩评论