Update a row with data from another row within the same table

Question

UPDATE user SET 
                tw_oauth_token=(SELECT tw_oauth_token FROM user WHERE id=27),
                tw_oauth_token_secret = (SELECT tw_oauth_token_secret FROM user WHERE id=27),
                tw_user_id = (SELECT tw_user_id FROM user WHERE id=27),
                handler = (SELECT handler FROM user WHERE id=27),
                merged=1 WHERE id=26

The idea is to select data from user table where id = 27 and update the same table where id = 26.

I am having the following error:

#1093 - You can't specify target table 'user' for update in FROM clause

Any help would be appreciated. Thanks

First result on google, first related link: stackoverflow.com/questions/45494/… — Matt Dodge
– Matt Dodge, Commented Apr 16, 2013 at 6:41

John Woo · Accepted Answer · 2013-04-16 06:51:00Z

1

UPDATE  user a
        CROSS JOIN user b
SET     a.tw_oauth_token = b.tw_oauth_token,
        a.tw_oauth_token_secret = b.tw_oauth_token_secret,
        a.tw_user_id = b.tw_user_id,
        a.handler = b.handler,
        a.mrged = 1
WHERE   a.ID = 26 AND
        b.ID = 27

edited Apr 16, 2013 at 6:51

answered Apr 16, 2013 at 6:42

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jim Dennis Over a year ago

I don't understand why this is a CROSS JOIN rather than a NATURAL or INNER JOIN; it seems like the salient part is that it's a self-JOIN user AS a joined to user AS b --- but where the "AS" syntactic sugar is being left out. (Personally I prefer to see the "AS" keyword, especially in discussion and documentation).

John Woo Over a year ago

@JimDennis you cannot use INNER JOIN here since you cannot determine where the record will be joined. here's what happens with the statement above: sqlfiddle.com/#!2/11f5f/2

John Woo Over a year ago

@JimDennis the result if you use NATURAL JOIN sqlfiddle.com/#!2/beb51/2 which doens't give you correct data.

John Woo Over a year ago

@JimDennis lastly, I'm not fun of using AS myself as it is optional.

Jim Dennis Over a year ago

Thanks for the sqlfiddle links. I really need to start using that.

Cosmin Ionascu · Accepted Answer · 2013-04-16 06:44:53Z

1

Why don't you try to create a virtual table based on your table then update the user table with SELECT statements on that View. Something like this:

CREATE VIEW view_user AS
SELECT *
FROM user;

And then use the view_user in the update.

answered Apr 16, 2013 at 6:44

Cosmin Ionascu

7,7185 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

Update a row with data from another row within the same table

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related