Geek Answers Handbook

Mysql - UPDATE with LIMIT x, y

UPDATE table SET checked = 1 WHERE field = 'xxx' LIMIT 1

works fine but

 UPDATE table SET checked = 1 WHERE field = 'xxx' LIMIT 1, 10

throw error "# 1064 - You have an error in the SQL syntax, check the manual that matches the version of your MySQL server for the correct syntax used next to" 10 "on line 1"

Why is this impossible? I want to update everything except the first line.

+4
source share
3 answers

LIMIT in the UPDATE clause is simply the upper limit on how many rows can be updated.

This is not like SELECT , where you can ignore everything except a specific sub-range of result rows that you need to deal with.

If you really need something like this, you should use VIEW with a LIMIT constraint and do UPDATE .

+6
source 
 update table set checked = 1 where id in (select * from (select id from table where field = 'xxx' order by id limit 1, 10) as t)
+8
source

I had a similar situation, but in my case I needed to update only 2 rows ordered by a numerical identifier, so my query would be like this:

UPDATE myTable SET Column1='some data',Column2='some othe data' WHERE Column3='some criteria' LIMIT 1;

UPDATE myTable SET Column1='some data',Col2='some othe data' WHERE Column3='some criteria2' ORDER BY ID DESC LIMIT 1;

Note. The first query implicitly selects the first matching row in the table, and the second query selects the second matching row, obviously changing the order. He does not answer the question, but can help someone with a problem similar to mine.

0
source

More articles:


All Articles