MySQL duplicate entry when updating multiple rows

Question

I have a table of items where each item has an itemOrder. itemOrder is part of the PRIMARY KEY. I'm trying to write an UPDATE that will reorder the items.

For example, if I wanted to move item 3 to be item 1, item 3's new itemOrder would be 1, item 1's would be 2, and item 2's would be 3. This query works in SQL Server since UPDATE statements are transactional.

UPDATE tbl_items
SET itemOrder = CASE WHEN itemOrder = 3 THEN 1 ELSE itemOrder + 1 END
WHERE itemOrder BETWEEN 1 and 3;

In MySQL, it appears that UPDATEs occur a row at a time, and if at any point a duplicate PK exists, it throws an error. There must be some way to get around this, right? I tried the answers to sql swap primary key values to no avail. I also tried wrapping the statement in a START TRANSACTION;/COMMIT; but it had the same error.

I'm guessing the MySQL solution has something to do with ON DUPLICATE KEY, but I haven't been able to wrap my mind around it, nor how I could use it in my scenario.

score 0 · Answer 1 · answered Apr 14 '19 at 20:04

0

By default, MySQL operates in "auto commit" mode; you need to create a transaction manually:

begin;
update ...;
update ...;
commit;

Or, you can turn off auto commit before your updates.

set autocommit = 0;

and (optionally) turn it back on again after your updates:

set autocommit = 1;

answered Apr 14 '19 at 20:04

Bohemian

412,405
93
575
722

This didn't work for me. I tried using both `BEGIN;` and `START TRANSACTION;`, setting `autocommit` both inside and outside the transaction. I only used one UPDATE statement though to do all the rows, but I don't think that would make a difference. – dx_over_dt Apr 14 '19 at 20:09

score 0 · Accepted Answer · answered Apr 17 '19 at 16:41

It's a very hacky work-around, and if someone has the "correct" answer, I'll gladly mark it as such, but here's my solution until then.

DELIMITER //
CREATE PROCEDURE `sp_move_item` (
    fromOrder INT,
    toOrder INT
)
BEGIN

-- @shift is the amount each other item needs to move to make space 
-- for the target item: -1 for moving "up" and 1 for moving "down"
SELECT @low := CASE WHEN fromOrder > toOrder THEN toOrder ELSE fromOrder END,
    @high := CASE WHEN fromOrder < toOrder THEN toOrder ELSE fromOrder END,
    @shift := CASE WHEN fromOrder > toOrder THEN 1 ELSE -1 END;

START TRANSACTION;

-- Get the itemOrder of the item at the bottom of the list and add 1.
-- Setting items' itemOrder to values ≥ @offset guarantees no overlap
-- with the unaffected items of the table.
SELECT @offset := MAX(itemOrder) + 1
FROM tbl_items;

-- Set the itemOrder of all affected items to be at the end of the
-- table in the same order they are already in. Guarantees no overlap 
-- with each other.
UPDATE tbl_items
SET itemOrder = itemOrder + @offset
WHERE itemOrder BETWEEN @low AND @high;

-- Move the affected items back into place in the desired order.
UPDATE tbl_items
SET itemOrder = CASE 
    WHEN itemOrder = fromOrder + @offset THEN toOrder 
    ELSE itemOrder + @shift - @offset END
WHERE itemOrder >= @offset;

COMMIT;
END //
DELIMITER ;

This method has terribly poor performance as it needs to reorder the index 2 * (@high - @low) times. The more rows in the table, the greater the performance hit, even if @high - @low = 1 (a simple swap) as it currently is in my case.

A faster method (though terribly ugly code-wise) if there is only one index on the table and you're only doing a 1-position swap, would be to copy every column value of the row being moved into variables, overwrite that row's values with the other affected row, and then update that row to the variables' values, just as you would do for swapping values in an array.

Note: This code assumes that itemOrders are sequential with no missing values, though I think it will still work even if that's not the case. I haven't thought it through, though, so your mileage may vary.

MySQL duplicate entry when updating multiple rows

2 Answers2