Subtracting same value from selected fields in SQL

Looking to subtract the same value (512) from all rows with a primary key greater than 3599, in order to have the primary integer key be sequential. Due to a botched import, the auto-increment, primary-key field has an unwanted gap of 512

The following code works perfectly to update every value, but it doesn’t work for a selected range of values

UPDATE table
SET value = value - 512

How can the code be modified such that it only acts upon the rows with a value greater than 3599?

>Solution :

UPDATE table
SET value = value - 512
WHERE VALUE > 3599
ORDER BY value;

You need to use ORDER BY to ensure that it won’t create duplicate values while it’s going. Without this, it might try to replace 5000 with 4488 before it replaces 4488 with 3976.

Leave a Reply