UPDATE SQL查询中的LIMIT偏移量或OFFSET [英] 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  |

我想用X1替换前两个出现的X，然后用X2替换下一个出现的4个，使结果表看起来像这样:

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.

我想做这样的事情:

UPDATE table SET column = 'X' WHERE column = 'X2' LIMIT 90, 88

但是不幸的是，MySQL似乎在UPDATE查询中不支持OFFSET ...有什么办法吗?

but unfortunately MySQL doesn't seem to support OFFSET in UPDATE queries... Is there any way to do this?

推荐答案

尝试一下:

UPDATE table SET column = 'X1' WHERE id IN(SELECT id FROM (SELECT id FROM table WHERE column = 'X' LIMIT 2) as u);

然后

UPDATE table SET column = 'X2' WHERE id IN(SELECT id FROM (SELECT id FROM table WHERE column = 'X' LIMIT 4) as u);

这篇关于UPDATE SQL查询中的LIMIT偏移量或OFFSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！