Wrong update limits mysql

Question

From this question: update multiple rows using limit in mysql? I made this code up for a MySQL query:

UPDATE clientes SET telemarketer =1
WHERE telemarketer IN (
     SELECT telemarketer FROM (
         SELECT telemarketer FROM clientes 
         WHERE telemarketer=0
         ORDER BY telemarketer DESC
         LIMIT 0, 10
     ) temporal
 );

But It's returning a SELECT telemarketer FROM clientes.

Looking around I found out that the ORDER BY is needed in the case or it would return random rows.

Why isn't the LIMIT working?.

Already tryed using LIMIT 10 instead of LIMIT 0, 10 and got the same result.

Joachim Isaksson · Accepted Answer · 2013-09-12T17:31:51.120

Let's start from the innermost select;

 SELECT telemarketer FROM clientes 
 WHERE telemarketer=0

...returns zero or more rows of 0's, since that's the only allowed value to return. The order by is irrelevant since all rows have the same value, and the limit only limits the number of zeroes to 10.

That means that your whole query;

UPDATE clientes SET telemarketer =1
WHERE telemarketer IN (
   SELECT telemarketer FROM (
      SELECT telemarketer FROM clientes 
      WHERE telemarketer=0
      ORDER BY telemarketer DESC
      LIMIT 0, 10
   ) temporal
);

...turns into;

UPDATE clientes SET telemarketer =1
WHERE telemarketer IN (0, 0, ..., 0);

If there are more than 10 rows where telemarketer is 0, they'll all be returned, no matter the limit of the inner query.

What you probably want is to use a unique field (the primary key?) to identify the rows you want to update;

UPDATE clientes SET telemarketer=1 
WHERE primary_key IN (
  SELECT primary_key FROM (
    SELECT primary_key FROM clientes WHERE telemarketer=0 LIMIT 10
  ) a
)

An SQLfiddle to test with.

I get it, thanks, but then how do I return a group of 10 (it will be a variable later) records with telemarketer = 0 ? should I include some other field on the table to limit ? — Gonzalo Acosta, Sep 12 '13 at 17:20
@GonzaloAcosta If you have a primary key on the row, use that instead, updated the answer with a sample query. — Joachim Isaksson, Sep 12 '13 at 17:28
It works!. Thanks a lot. I guess I'll have to have a look onto temp tables to manage them better. — Gonzalo Acosta, Sep 12 '13 at 20:05

Parixit · Answer 2 · 2013-09-12T17:31:33.650

1

Try to do group_concat to get comma separated values used for IN

UPDATE clientes SET telemarketer =1
WHERE telemarketer IN (
     IFNULL(GROUP_CONCAT(
         SELECT c.telemarketer FROM clientes c 
         WHERE c.telemarketer=0
         ORDER BY c.telemarketer ASC
         LIMIT 10
     ),0) 
 );

Here we check for NULL values that if there would be 0 rows then we replace it with 0.

edited Sep 12 '13 at 17:31

answered Sep 12 '13 at 17:13

Parixit

3,829
3
37
61

Already tryied your code, it's returning the same result, it gives me the whole table when I want just the 10 first records. – Gonzalo Acosta Sep 12 '13 at 17:22
Still doesn't work. I already solved it but I'm curious about something, shouldn't it be `FROM clientes AS c` ? It's throwing a syntax error on line 4 (` SELECT c.telemarketer FROM clientes AS c `) – Gonzalo Acosta Sep 12 '13 at 20:08
If you make an alias of table then no need to use `AS` keyword. For column name you can use it. – Parixit Sep 13 '13 at 04:14

score 0 · Answer 3 · answered Sep 12 '13 at 17:23

The problem is in how you're using the subquery in the UPDATE. From http://dev.mysql.com/doc/refman/5.6/en/subquery-restrictions.html:

In general, you cannot modify a table and select from the same table in a subquery.

That means you can't use such a subquery in your UPDATE statement. What you'll need to do is break it into two or more statements, the first to build a temp table with a list of telemarketers:

CREATE TEMPORARY TABLE temporal (telemarketer int);

INSERT INTO temporal
     SELECT telemarketer FROM clientes 
     WHERE telemarketer=0
     ORDER BY telemarketer DESC
     LIMIT 0, 10

Then you can use this table in your update:

UPDATE clientes SET telemarketer =1
WHERE telemarketer IN (SELECT telemarketer FROM temporal);

DROP TABLE temporal;

Wrong update limits mysql

3 Answers3