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: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause — Matt Dodge, Apr 16 '13 at 06:41

John Woo · Accepted Answer · 2013-04-16T06:51:00.613

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 '13 at 06:51

answered Apr 16 '13 at 06:42

John Woo

258,903
69
498
492

1

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). – Jim Dennis Apr 16 '13 at 06:49
@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: http://sqlfiddle.com/#!2/11f5f/2 – John Woo Apr 16 '13 at 06:55
@JimDennis the result if you use `NATURAL JOIN` http://sqlfiddle.com/#!2/beb51/2 which doens't give you correct data. – John Woo Apr 16 '13 at 06:56
@JimDennis lastly, I'm not fun of using `AS` myself as it is optional. – John Woo Apr 16 '13 at 06:57
Thanks for the sqlfiddle links. I really need to start using that. – Jim Dennis Apr 16 '13 at 07:58

score 1 · Answer 2 · answered Apr 16 '13 at 06:44

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 '13 at 06:44

Cosmin Ionascu

7,018
5
25
42

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

2 Answers2