changing existing duplicate entries in mysql

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...

开发者_C百科also, is there a way to get highest value for column besides testing it in every row via php?

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