How to Swap Row Values in MySQL with a Unique Constraint?

MySQL Swapping Row Values with Unique Constraint

In MySQL, swapping priority values of two rows with a unique constraint on the priority column can be a challenge.

Problem:

When using this statement to swap values:

UPDATE tasks 
SET priority = 
CASE
    WHEN priority=2 THEN 3 
    WHEN priority=3 THEN 2 
END 

WHERE priority IN (2,3);

an error occurs due to a duplicate entry violation of the unique constraint.

Solution:

Unfortunately, swapping values without using multiple queries or bogus values is not possible in MySQL. This is because MySQL enforces constraint checking after each row update, unlike some other DBMS.

Alternative Approach:

To swap values without violating the constraint, you can use the following steps within a transaction:

1. Update rows with bogus values: Set the priority of one row to a non-existent negative value (-2), and the other row to another negative value (-3).
2. Swap values using negative values: Negate the bogus values (-priority) to exchange the priorities.
3. Commit the transaction: To apply the changes permanently.

Transaction Statement:

START TRANSACTION ;
    UPDATE tasks 
    SET priority = 
      CASE
        WHEN priority = 2 THEN -3 
        WHEN priority = 3 THEN -2 
      END 
    WHERE priority IN (2,3) ;

    UPDATE tasks 
    SET priority = - priority
    WHERE priority IN (-2,-3) ;
COMMIT ;

Note:

This approach requires the unique column to be a signed integer with no negative values. If negative values are allowed, a different solution may be necessary.

The above is the detailed content of How to Swap Row Values in MySQL with a Unique Constraint?. For more information, please follow other related articles on the PHP Chinese website!