How to add +1 to already existing values

Question

I have table with the column name displayorders with values 1 to 250 and it's not autoincrement.

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?

Answer 1

If I understood correctly, you'd want to run an UPDATE statement like this:

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 UPDATE statement:

SELECT * FROM your_table;
+--------------+
| displayorder |
+--------------+
|            1 |
|            2 |
|            4 |
|            5 |
|            6 |
|            7 |
|            8 |
|            9 |
|           10 |
+--------------+
9 rows in set (0.00 sec)

Answer 2

UPDATE MyTable SET displayorders=displayorders+1 WHERE displayorders>2

Answer 3

update yourTableName set displayorder = displayorder + 1 where displayorder > 2