updating a column value in sql that start from las

Question

I have a table having column name ID(primary key).

in this I have values from 1 to 152.

now I want to update the table so that the id ranging from 109 to 152 incremented by 1.

when I run this sql

 update category set id=(id+1) where id<152 and id>108

it give error because when it update ID=109 to 110 then it found duplicate of 110 in next row.

so how can I do that.

can I start updating from last row that is 152 and stop it on 108.

Answer 1

Do it in two steps. First add 10,000 (or any other unused range) and then subtract 9999. Do not forget to adjust the criteria in the second step.

Answer 2

I am guessing that you are using MySQL. If so, you can do this by using order by.

update category
    set id = id + 1
    where id > 108 and id < 152
    order by id desc;

Assuming the ids are positive, the following should work in any database (assuming the column is not declared "unsigned"):

update category
    set id = -(id + 1)
    where id > 108 and id < 152;

update category
    set id = -id
    where id < 0;