MySQL update with a subquery

Question

I am trying to update a specific row in a table based on another value in that table, but I can't seem to figure out how to do it:

UPDATE users AS a SET a.val = (SELECT value FROM users WHERE userid = 4) WHERE a.userID = 1

but I am getting the error

Lookup Error - MySQL Database Error: You can't specify target table 'a' for update in FROM clause

Any ideas what I am missing here?

peterm · Accepted Answer · 2013-08-15 19:27:33Z

1

Use JOIN syntax and a non-equi join

UPDATE users a JOIN users b
    ON a.userID = 1
   AND b.userid = 4
   SET a.value = b.value

Here is SQLFiddle demo

answered Aug 15, 2013 at 19:27

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

rjbogz Over a year ago

Thank you! Slightly different from SQL server, which is what I'm used to.

Tulip · Accepted Answer · 2023-12-06 18:01:56Z

0

use join and subquery,

update users as a
join (select value  
      from users 
      where userid=4) as sub
set a.val=sub.val
where a.userID=1;

The subquery will retrieve the value from the users table where userid=4 and then joins this subquery with the users table where userID=1 and updates the val column.

answered Dec 6, 2023 at 18:01

Tulip

213 bronze badges

Collectives™ on Stack Overflow

MySQL update with a subquery

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related