The statement succeeds if it updates id values first by setting 2 to 1 and then 3 to 2. However, it fails if it first tries to set 3 to 2. That would result in two records having an id value of 2, so a duplicate-key violation occurs. To solve this problem, add an ORDER BY clause to cause the row updates to occur in a particular order: UPDATE people SET id=id-1 ORDER BY id; Row contents will be as follows; +-------------------+ | id | name | age | +-------------------+ | 1 | Victor | 21 | | 2 | Susan | 15 | +-------------------+
“The best way to be ready for the future is to invent it.”— John Sculley