Joe Celko's SQL for Smarties, 3rd Edition

24.7. Swapping and Sliding Values in a List

You will often want to manipulate a list of values, changing their sequence position numbers. The simplest such operation is to swap two values in your table.

CREATE PROCEDURE SwapValues
(IN low_seq_nbr INTEGER, IN high_seq_nbr INTEGER)
LANGUAGE SQL
BEGIN -- put them in order
SET low_seq_nbr
    = CASE WHEN low_seq_nbr <= high_seq_nbr
           THEN low_seq_nbr ELSE high_seq_nbr;
SET high_seq_nbr
    = CASE WHEN low_seq_nbr <= high_seq_nbr
           THEN high_seq_nbr ELSE low_seq_nbr;
UPDATE Runs -- swap
   SET seq_nbr = low_seq_nbr + ABS(seq_nbr - high_seq_nbr)
 WHERE seq_nbr IN (low_seq_nbr, high_seq_nbr);
END;

Inserting a new value into the table is easy:

CREATE PROCEDURE InsertValue (IN new_value INTEGER) LANGUAGE SQL INSERT ...