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;