Ignore unique key while updating some rows (mariaDB)

Question

I have the following table (mariaDB):

+----+--------------+-------------+-------------+
| id | content_type | sort_number | document_id |
+----+--------------+-------------+-------------+
|  1 | text         |           1 |           1 |
|  2 | table        |           2 |           1 |
|  3 | text         |           3 |           1 |
|  4 | image        |           4 |           1 |
+----+--------------+-------------+-------------+

The combination of sort_number and document_id is unique.

Now when i want to add a new entry at position 2, I need to increment the sort_number of all entries where the sort_number >= 2 by one step.

To achieve this, i use the following query:

update `table_name` set `sort_number` = sort_number +1 where `sort_number` > ? and `document_id` = ?

But because of the unique key (sort_number and document_id) i get an error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '3' for key 'table_name_sort_number_document_id_unique'

I tired to avoid the error with SET unique_checks=0; but still get the error...

Is there a (better) way to update the sort_number in one query?

You need to get the max(sort_number) for document_id and then increment the sort_number of that row — karen, Aug 02 '18 at 16:29

score 1 · Answer 1 · answered Aug 02 '18 at 16:27

1

ORDER BY also applies to update query, so simply:

SET @i:=0;
UPDATE items SET disp_order=@i:=@i+1 ORDER BY item_name;

Simply start updating from the last row and traverse backwards.

answered Aug 02 '18 at 16:27

Faran Ali

482
3
12

score 0 · Answer 2 · answered Aug 03 '18 at 07:15

0

I like the solution provided by Paul Spiegel. My Query looks now like this:

update `table_name` set `sort_number` = sort_number +1 where `sort_number` > ? and `document_id` = ? order by `sort_number` desc

answered Aug 03 '18 at 07:15

Nio

497
3
6
16

score 0 · Answer 3 · answered Nov 21 '21 at 14:25

0

UPDATE IGNORE is the answer

UPDATE IGNORE `table_name` set `sort_number` = sort_number +1 where `sort_number` > ? and `document_id` = ?

answered Nov 21 '21 at 14:25

user2573099

63
1
6

Ignore unique key while updating some rows (mariaDB)

3 Answers3