MySQL 如何将 +1 添加到现有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3766423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add +1 to already existing values
提问by svk
I have table with the column name displayorders with values 1 to 250 and it's not autoincrement.
我有一个列名 displayorders 的表，其值为 1 到 250，它不是自动增量。
Now I want to add a new row in table with displayorder= 3. So I don't want to manually update all the values form 3 to 250.Instead of that I want to update all the displayorders to +1 and I can manually change from 1 to 2(ie 2 to 3 after updation).How can I do it through SQL Query?
现在我想在表中添加一个新行displayorder= 3。所以我不想手动将所有值从 3 更新为 250。相反，我想将所有显示顺序更新为 +1，我可以手动更改1 到 2（即更新后的 2 到 3）。我如何通过 SQL 查询做到这一点？
回答by Daniel Vassallo
If I understood correctly, you'd want to run an UPDATEstatement like this:
如果我理解正确，你会想要运行这样的UPDATE语句：
UPDATE your_table SET displayorder = displayorder + 1 WHERE displayorder > 2;
Test case:
测试用例：
CREATE TABLE your_table (displayorder int);
INSERT INTO your_table VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);
Result after the UPDATEstatement:
UPDATE语句后的结果：
SELECT * FROM your_table;
+--------------+
| displayorder |
+--------------+
| 1 |
| 2 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+--------------+
9 rows in set (0.00 sec)
回答by Purge
UPDATE MyTable SET displayorders=displayorders+1 WHERE displayorders>2
回答by Beth Whitezel
update yourTableName set displayorder = displayorder + 1 where displayorder > 2
更新 yourTableName set displayorder = displayorder + 1 其中 displayorder > 2
