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
Best Answer
I think You can perform update You need using 2 statements.
1) Update all records in decreasing order. Single-table UPDATE allows ordering.
2) Restore values for first 2 records.