CODEWITHSUNDEEP
mysqlduplicates
Mladen
Mladen

Reputation: 1245

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

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

Upvotes: 1

Views: 144

Answers (1)

Tatu Ulmanen
Tatu Ulmanen

Reputation: 124768

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

Upvotes: 2

Related Questions