MySQL - Update column values using subquery select itself

Question

Please help me with MySQL update to update the column with result returns from select itself.

For instance, I have two tables

TABLE user( userid int, groupid int )

TABLE thread ( threadid int, userid int, sticky tinyint, vip tinyint )

Now I'm trying to achieve this with a single update query, but can't seem to do it. What I thought I should do is:

UPDATE user SET groupid=15 WHERE userid IN (SELECT userid FROM thread t LEFT JOIN user u ON u.userid=t.userid WHERE (t.sticky=1 AND t.vip=1) AND (u.groupid=11 OR u.groupid=14) GROUP BY t.userid);

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

Please help me!

Generally, in the absence of any aggregating functions, a GROUP BY clause is a bad idea. MySQL forgives (and even optimizes) the aberration, but it often leads to unexpected or erroneous results. Also, unless you include an IS NULL comparison, an LEFT JOIN on a table from which you select no columns is just plain weird! — Strawberry, Oct 07 '14 at 08:06
possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Juru, Oct 07 '14 at 08:07
GROUP BY because of the result returns duplicate userid. How can I solve the problem, with single query only? — hatxi, Oct 07 '14 at 08:21

Abhishek Gupta · Accepted Answer · 2014-10-07T09:06:16.837

1

It can be done by generating a new table from left join of two tables and then update from the filtered result, syntax will be as follows:

UPDATE user AS nu 
 INNER JOIN
  (SELECT u.userid, u.groupid 
  FROM thread t LEFT JOIN user u 
      ON u.userid=t.userid 
  WHERE (t.sticky=1 AND t.vip=1) AND 
       (u.groupid=11 OR u.groupid=14) 
  GROUP BY t.userid) AS my_table
 ON nu.userid = my_table.userid 
SET nu.groupid = 15;

edited Oct 07 '14 at 09:06

answered Oct 07 '14 at 08:26

Abhishek Gupta

4,066
24
27

Thanks for your reply. But the error returns: #1288 - The target table my_table of the UPDATE is not updatable – hatxi Oct 07 '14 at 08:35
@hatxi: now it should work, in earlier case a new table my_table was formed which is just a temporary table from outcome of left join. – Abhishek Gupta Oct 07 '14 at 08:47
Thank you Rockse, it returns error: #1052 - Column 'userid' in field list is ambiguous – hatxi Oct 07 '14 at 08:52
it's still error: #1054 - Unknown column 'u.userid' in 'on clause' – hatxi Oct 07 '14 at 09:03
please add a sqlfiddle, it some silly syntax error which could be solved easily. – Abhishek Gupta Oct 07 '14 at 09:04

score 0 · Answer 2 · answered Oct 07 '14 at 08:07

0

Try using the following:

UPDATE user u2 SET u2.groupid=15 WHERE u2.userid IN (SELECT userid FROM thread t LEFT JOIN user u ON u.userid=t.userid WHERE (t.sticky=1 AND t.vip=1) AND (u.groupid=11 OR u.groupid=14) GROUP BY t.userid);

This should do the trick, hope it helps :)

answered Oct 07 '14 at 08:07

zozelfelfo

3,776
2
21
35

#1093 - You can't specify target table 'u2' for update in FROM clause :( – hatxi Oct 07 '14 at 08:13
sorry to hear that, I'll do some research and change my answer – zozelfelfo Oct 07 '14 at 08:18

Strawberry · Answer 3 · 2014-10-07T09:31:39.017

0

Is your desired query materially different from this...

UPDATE user u
  JOIN thread t 
    ON t.userid = u.userid 
   SET groupid = 15
 WHERE t.sticky = 1 
   AND t.vip = 1
   AND u.groupid IN(11,14);

?

If so, then as Rockse suggests, consider providin proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem, together with a corresponding desired result set.

edited Oct 07 '14 at 09:31

answered Oct 07 '14 at 09:18

Strawberry

33,750
13
40
57

score 0 · Answer 4 · answered Oct 07 '14 at 09:21

0

update user1 u
left join thread1 t on t.userid = u.userid
where (t.sticky=1 AND t.vip=1) AND (u.groupid=11 OR u.groupid=14) 
set u.groupid = 15
GROUP BY t.userid;

Use this

answered Oct 07 '14 at 09:21

user3614846

1

score -1 · Answer 5 · answered Oct 07 '14 at 08:06

-1

You can add this before your query:

use yourdatabase;

yourdatabase is database name which includes user table

answered Oct 07 '14 at 08:06

DragonK

59
1
9

MySQL - Update column values using subquery select itself

5 Answers5