[FIXED] How to change the update order while using the SQL UPDATE statement?

Issue

I have a table where each row is numbered with a unique index(1,2,3,...). I want to increase the index of each entry by 4, in order to make space for four new entries at the beginning(indexes 1,2,3 and 4).

I tried using:

UPDATE table SET key_index = key_index + 4;

But it results in an ERROR because the index must be, as said, unique(increasing 1 by 4 results in 5. Index 5 could already exist).

If I could use the UPDATE statement, starting at the bottom(from highest to lowest index), then there would be such ERROR. Is it possible to use an UPDATE statement like that?

Solution

This can be tricky. One trick that I use is two updates -- first to a range of values that are "safe". Second to the desired values:

update t
    set key_index = - key_index ;

update t
    set key_index = (- key_index) + 4;

You can also drop the unique index/constraint -- or disable it in some databases.

Answered By - Gordon Linoff

Answer Checked By - Clifford M. (PHPFixing Volunteer)