CODEWITHSUNDEEP
mysql
Qiao
Qiao

Reputation: 17049

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 your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 10' at line 1"

Why it is not possible? I want to update everything except first row.

Upvotes: 4

Views: 12362

Answers (3)

Qiniso
Qiniso

Reputation: 2629

I had a similar situation, but in my case I needed to update only 2 rows ordered by a numerical ID, so my query would've been 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 by explicitly reversing the order. It doesn't answer the question but it may benefit someone with problem similar to mine.

Upvotes: 0

Nicola Cossu
Nicola Cossu

Reputation: 56407

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)

Upvotes: 9

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385395

LIMIT in an UPDATE clause is merely an upper limit on how many rows may be updated.

It's not like in a SELECT where you can ignore all but a certain subrange of result rows to deal with.

If you really need something like this, you should use a VIEW with the LIMIT restriction, and perform the UPDATE on that.

Upvotes: 6

Related Questions