changing existing duplicate entries in mysql

Question

sorry for the (probably) noob question, but I', new at this stuff.

i have a table that has column 'position', and I want to, when inserting a new row, change positions of every row that has position lower than row that is inserted. for example, if I add row with position 4, if there is a duplicate, it should become 5, 5 should shift to 6 and so on...

also, is there a way to get highest value for column besides testing it in every row via php?

Answer 1

You need two queries. Assuming you know the position you're inserting, increase the position of each row that has position greater than or equal to the position you're inserting:

UPDATE table SET position = position + 1 WHERE position >= newPosition

After that, the newPosition can be inserted and no duplicates will exist:

INSERT INTO table SET position = newPosition

To get the highest value, you can use MAX()

SELECT MAX(position) FROM table