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.

Answer 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.

Answer 2

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.