CODEWITHSUNDEEP
mysql
Thomas Williams
Thomas Williams

Reputation: 1546

Update order by limit is giving me an error

I want to update a table using order by and limit. The reason is that I want to offset the first 3 entries. I started by making a query to list what I wanted to update, and that works.

SELECT `Name`,`active`
FROM `tlogon`
WHERE `active`>0 ORDER BY `id` LIMIT 3,18446744073709551610

So I have 8 items in my table and it is showing the last 3 which is correct, because 2 of the rows have an active of zero. So then I tried to turn my select into an update like this.

UPDATE `tlogon` SET `active`=3
WHERE `active`>0 ORDER BY `id` LIMIT 3,18446744073709551610

The idea here is that it gets a list of active and ignores the first 3, and then I only want to update after the first three. However the above code gives an error in phpmyadmin.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18446744073709551610' at line 2

So my question is. Why does my select work, but my update query fail?

EDIT

Ok I have tried something else, but again I have failed. My id is autoincrementing so I tried the following code

UPDATE `tlogon` SET `active`=3
Where `id`>=(
    SELECT `id`
    FROM `tlogon`
    WHERE `active`>0 ORDER BY `id` LIMIT 3,1
)

Now it works in simulation, but the problem when I do it for real I get this error.

#1093 - You can't specify target table 'tlogon' for update in FROM clause

Which is annoying because what I am doing is getting the first id above my limit and changing them, but again I have failed.

So then I thought about a Right Join like this

UPDATE `tlogon` AS `a`
RIGHT JOIN `tlogon` AS `b` ON `a`.`id`>`b`.`id`
SET `a`.`active`=3
WHERE (
   SELECT `id`
    FROM `b`
    WHERE `tlogon`.`active`>0 ORDER BY `tlogon`.`id` LIMIT 3,1 
 )

But I think I am doing something wrong because now it is telling me that table b does not exist.

Upvotes: 0

Views: 62

Answers (2)

Thomas Williams
Thomas Williams

Reputation: 1546

Ok I have an answer, but it only works if you are using an auto incrementing id.

UPDATE `tlogon` AS `a`
INNER JOIN
     (SELECT `id`
    FROM `tlogon`
    WHERE `active`>0 ORDER BY `id` LIMIT 3,1) as b  ON `a`.`id` >= `b`.`id`
SET `a`.`active`=3

because you cannot reference the same table with the select I ended up using an inner join to the same table.

Basically I am getting the next highest id in my where clause and only changing active if the id is higher. Thus the first 3 entries will be ignored and the rest will be changed to 3, which is what I wanted.

Upvotes: 0

axiac
axiac

Reputation: 72396

The UPDATE statement does not support an offset for LIMIT, only the number of rows to update.

Its syntax is:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
   SET col_name={expr | DEFAULT},
    [, col_name={expr | DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

You need to find another way to ignore the first 3 rows.

Upvotes: 1

Related Questions