Search code examples
mysqlsqllimitoffset

LIMIT offset or OFFSET in an UPDATE SQL query

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?

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