Hi

I need help to create a smart SQL query for MySQL, to "which place" of two rows, without having to devide it into 4 queries. I'll recieve the ID of a row, and I want to decrease the field 'Order' of that row with 1, and at the same time, increase the Order of the row which currently have that Order number.
Ex.

ID  - Order - Other fields
-------------
106 - 4 - [other1]
134 - 5 - [other2]
194 - 6 - [other2]

which place... =>

ID  - Order
-------------
106 - 5 - [other1]
134 - 4 - [other2]
194 - 6 - [other2]

I should add that the field Order is Unique, so two of the rows can't have the same Order, evan if just temporarily (right?). I can't think of any smart way of doing this, only to first retrieve the Order of the given ID, then set it to something temporary (like -1), then increase the Order of the other row, and finaly set the correct Order of the row with the given ID.

Any help is appreciated
lizciz

A stored procedure would be the only way to handle this scenario, and as you surmise would need the existing values setting to intermediate values before completing the update.