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.