开发者

MYSQL Update statement - unknown column

I have a select query which works correctly with the below output:

MYSQL Update statement - unknown column

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:

MYSQL Update statement - unknown column

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜