Algorithm for managing position in SQL table

Question

I have a small doubt, how to manage the following situation. In a DB table i save the relevant data in different columns and at the end i save the exact order in a column called position. This table is binded to a grid. Now I'm implementing the functionality that will make change the position to the rows, but I need still to update the DB. I was asking what is the best practice or the most convenient way to achieve this?

And suggestion is welcome.

Example

Name position

abc 1

def 2

ghj 3

Now I click on the button and I get the following

Name position

def 1

abc 2

ghj 3

I hope that I have explained myself!

Based on the comments this is the real example:

TABLE1 with 3 columns ID1, ID2, POSITION
ID1 AND ID2 are FK's and the table has a PK based on ID1 and ID2
When I select the data to display I use the following query SELECT NAME, ETC FROM TABLE2 INNER JOIN TABLE1 ON ID1 = ID1 WHERE ID2 = 511 ORDER BY POSITION

Now I need to change the position on two elements so the row in TABLE1 for example 311,511,5 needs to became 311,511,4 and the row that was 433,511,4 needs to became 433,511,5. The opposite.

I hope that this helped to clear out my question.

Cheers!

Answer 1

When you say: "change the position to the rows", do you mean moving rows up and down through the table (relative to position ordering)?

If so, to move the row up, you need to exchange the position with previous row. To move it down, you need to do the same with the next row. Depending on your particular SQL dialect, the syntax for exchanging values from different rows will probably look similar to this:

UPDATE YOUR_TABLE
SET position =
    CASE position
    WHEN 2 THEN 3
    WHEN 3 THEN 2
    END
WHERE position IN (2, 3)

This example moves the row 3 up and row 2 down. Replace 2 and 3 according to your needs...

BTW, this should work even if there is a UNIQUE constraint on position (confirmed under Oracle).

For more ideas, you may take a look at this question.