MySQL - Update column values using subquery select itself

0
votes

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!

5
mysql
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
possible duplicate of Mysql error 1093 - Can't specify target table for update in FROM clause - Juru
GROUP BY because of the result returns duplicate userid. How can I solve the problem, with single query only? - hatxi

5 Answers

1
votes

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;
0
votes

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 :)

0
votes
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
0
votes

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.

-1
votes

You can add this before your query:

use yourdatabase;

yourdatabase is database name which includes user table