LIMIT offset or OFFSET in an UPDATE SQL query
limit, mysql, offset, sql
Solution
Try this:
`UPDATE table SET column = 'X1' WHERE id IN(SELECT id FROM (SELECT id FROM table WHERE column = 'X' LIMIT 2) as u);`
and then
`UPDATE table SET column = 'X2' WHERE id IN(SELECT id FROM (SELECT id FROM table WHERE column = 'X' LIMIT 4) as u);`
Problem
I have a table similar to this: ``` | 0 | X | | 1 | X | | 2 | X | | 3 | Y | | 4 | Y | | 5 | X | | 6 | X | | 7 | Y | | 8 | Y | | 9 | X | ``` I'd like to replace first 2 occurrences of `X` with `X1`, and then 4 next occurrences with `X2` so that the resulting table looks like this: ``` | 0 | X1 | | 1 | X1 | | 2 | X2 | | 3 | Y | | 4 | Y | | 5 | X2 | | 6 | X2 | | 7 | Y | | 8 | Y | | 9 | X2 | ``` The table in question is of course much bigger and the number of occurrences would thus be higher too so manual editing is not a solution. I'd like to do something like this: `UPDATE table SET column = 'X' WHERE column = 'X2' LIMIT 90, 88` but unfortunately MySQL doesn't seem to support OFFSET in UPDATE queries... Is there any way to do this?