Increment value for unique key column

Question

I have a table:

CREATE TABLE tbl (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    rank BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY rank (rank)
)

With some values:

(2,    11)
(1,    12)
(9,    42)
(4711, 43)
(9000, 44)

I.e the ranks may have gaps. The rank is always >= 1.

I want to increment every rank by one, starting by the 3rd least value:

UPDATE tbl table_1
JOIN (
    SELECT table_2.id
    FROM tbl table_2
    ORDER BY table_2.rank ASC
    LIMIT 2, 18446744073709551615
) sub_1 ON table_1.id = sub_1.id
SET table_1.rank = table_1.rank + 1

Problem:

#1062 - Duplicate entry '43' for key 'rank'

---

What I have tried:

• Adding ORDER BY:

  UPDATE tbl table_1
  JOIN (
      SELECT table_2.id
      FROM tbl table_2
      ORDER BY table_2.rank ASC
      LIMIT 2, 18446744073709551615
  ) sub_1 ON table_1.id = sub_1.id
  SET table_1.rank = table_1.rank + 1
  ORDER BY table_1.rank DESC
  

  #1221 - Incorrect usage of UPDATE and ORDER BY

• Eliminating the join:

  UPDATE tbl
  SET rank = rank + 1
  WHERE rank >= (
      SELECT table_2.rank
      FROM tbl table_2
      ORDER BY table_2.rank ASC
      LIMIT 2, 1
  )
  ORDER BY rank DESC
  

  #1093 - Table 'tbl' is specified twice, both as a target for 'UPDATE' and as a separate source for data

---

The only options I can think of are either omitting UNIQUE KEY, or splitting up the statement into two: get the 3rd least rank, then run UPDATE … ORDER BY ….

Is there any proper solution for my problem?

---

Server: MariaDB 10.2.15
InnoDB: 5.7.22

Answer 1

I think You can perform update You need using 2 statements.

1) Update all records in decreasing order. Single-table UPDATE allows ordering.

UPDATE tbl
SET rank=rank+1
ORDER BY rank DESC;

2) Restore values for first 2 records.

UPDATE tbl
SET rank=rank-1
ORDER BY rank ASC LIMIT 2;