Search code examples
mysqlsqlmysql-error-1093

Update with SELECT and group without GROUP BY


I have a table like this (MySQL 5.0.x, MyISAM):

response{id, title, status, ...} (status: 1 new, 3 multi)

I would like to update the status from new (status=1) to multi (status=3) of all the responses if at least 20 have the same title.

I have this one, but it does not work :

UPDATE response SET status = 3 WHERE status = 1 AND title IN (
  SELECT title FROM (
   SELECT DISTINCT(r.title) FROM response r WHERE EXISTS (
     SELECT 1 FROM response spam WHERE spam.title = r.title LIMIT 20, 1)
   )
  as u)

Please note:

  • I do the nested select to avoid the famous You can't specify target table 'response' for update in FROM clause
  • I cannot use GROUP BY for performance reasons. The query cost with a solution using LIMIT is way better (but it is less readable).

EDIT:

  • It is possible to do SELECT FROM an UPDATE target in MySQL. See solution here
  • The issue is on the data selected which is totaly wrong.
  • The only solution I found which works is with a GROUP BY:

    UPDATE response SET status = 3
     WHERE status = 1 AND title IN (SELECT title 
                                      FROM (SELECT title 
                                              FROM response 
                                          GROUP BY title 
                                            HAVING COUNT(1) >= 20) 
    

as derived_response)

Thanks for your help! :)


Solution

  • I would write something straightforward like below

    UPDATE `response`, (
      SELECT title, count(title) as count from `response`
         WHERE status = 1
         GROUP BY title
      ) AS tmp
      SET response.status = 3
      WHERE status = 1 AND response.title = tmp.title AND count >= 20;
    

    Is using GROUP BY really that slow ? The solution you tried to implement looks like requesting again and again on the same table and should be way slower than using GROUP BY if it worked.