Subtracting same value from selected fields in SQL-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

You have three problems.

First is the problem stated in the question: updated the existing keys. This is actually the easiest to solve:

UPDATE table
SET value = value - 512
WHERE value > 3599

But then we find the new problem: either new inserts are still going to pick up where they left off, and you've only shifted to the gap to later in the table, or you are using an unsafe mechanism to set the values, such as MAX(value) 1. One of those two things must be true.

Finally, the last problem is either these keys are leaking to the end user in an inappropriate way or you are caring too much about whether there is a gap at all. In real databases, gaps don't matter and are a normal part of operating the system.