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.
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)
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.
LIMIT 5, 10 but it obviously doesn't work. My previous comment was actually a bit of a reaction to @zerkms first comment that it isn't supported in mysql. That confused me, because the answer was accepted and the OP used the tag mysql. Hopefully I made some sense here :\LIMIT 5, 10.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.
