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 &gt;= (
    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;