CODEWITHSUNDEEP
mysqljoin
Smudger
Smudger

Reputation: 10809

MYSQL Update statement - unknown column

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

enter image description here

I would like to create an update statement to set the value of the despgoods_alldetails.loadid column = loaddetails.loadid where despgoods_alldetails_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: enter image description here

any idea how I can get this update statement to work?

Thanks, Ryan

Upvotes: 1

Views: 582

Answers (4)

linkamp
linkamp

Reputation: 215

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;

Upvotes: 1

Dimitre Radoulov
Dimitre Radoulov

Reputation: 28000

update  
  despgoods_alldetails as da
join 
  loaddetails as l
using 
  (despgoodsid)
set 
  da.loadid = l.loadid;

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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

Upvotes: 1

Mac
Mac

Reputation: 1642

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
)

Upvotes: 1

Related Questions