LIMIT offset or OFFSET in an UPDATE SQL query

Question

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?

Answer 1

I don't know whether you have id filed available in table or not, but you can use WHERE id BETWEEN 88 AND 90, MySQL does not support Offset in update query, but you can do this by limiting using BETWEEN command

Answer 2

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);