3

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 would strongly advise you to change the name of your rank field to something else (my_rank, the_rank, tbl_rank...) because with MySQL (8.02 and above), RANK is now a keyword (for analytic/window functions). Best to avoid this if possible! – Vérace May 25 '18 at 5:17
  • @Vérace, thank you for the hint! The actual statements are generated by sqlalchemy, which will `quote` names when needed. – kay May 25 '18 at 12:28
  • 1
    There must be a table with a field called rank though? Or have I misunderstood the ORM - whatever else confusing they did, I didn't think they renamed fields! – Vérace May 25 '18 at 12:51
  • @Vérace, no I could give it any name. (How about "index"? ;) ) I meant that sqlalchemy automatically escapes names using backticks when you use a reserved name, so you don't have to think for too long how you call your columns. The name "rank" makes sense semantically, that's why I chose it. But beware: Java's Hibernate does rename columns instead of escaping the names. (At least it did a few years ago when I used it.) – kay May 25 '18 at 13:08
  • 1
    Indeed - but what happens when you want to port? Avoid all 825 words here and you'll be fine :-) – Vérace May 25 '18 at 13:21
3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.