Increment value for unique key column

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

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;

StackExchange Database Administrators Q#207761, answer score: 4