How to swap values of two rows in MySQL without violating unique constraint?

by

Is it possible to accomplish this in MySQL without using bogus values and multiple queries?

No. (none that I can think of).

The problem is how MySQL processes updates. MySQL (in difference with other DBMS that implement UPDATE properly), processes updates in a broken manner. It enforces checking of UNIQUE (and other) constraints after every single row update and not – as it should be doing – after the whole UPDATE statement completes. That’s why you don’t have this issue with (most) other DBMS.

For some updates (like increasing all or some ids, id=id+1), this can be solved by using – another non-standard feature – an ORDER BY in the update.

For swapping the values from two rows, that trick can’t help. You’ll have to use NULL or a bogus value (that doesn’t exist but is allowed in your column) and 2 or 3 statements.

You could also temporarily remove the unique constraint but I don’t think that’s a good idea really.

So, if the unique column is a signed integer and there are no negative values, you can use 2 statements wrapped up in a transaction:

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 ;

More Related Contents:

  1. 1052: Column ‘id’ in field list is ambiguous
  2. Codeigniter – multiple database connections
  3. Column count doesn’t match value count at row 1
  4. How to add levenshtein function in mysql?
  5. How to search JSON data in MySQL?
  6. MySQL > Table doesn’t exist. But it does (or it should)
  7. Is the primary key automatically indexed in MySQL?
  8. How do I modify a MySQL column to allow NULL?
  9. Connect to mysql in a docker container from the host
  10. Is there ANY_VALUE capability for mysql 5.6?
  11. Cumulative sum over a set of rows in mysql
  12. Set NOW() as Default Value for datetime datatype?
  13. MySQL update CASE WHEN/THEN/ELSE [duplicate]
  14. No connection could be made because the target machine actively refused it (PHP / WAMP)
  15. MySQL SELECT DISTINCT multiple columns
  16. SQL DELETE with JOIN another table for WHERE condition
  17. How to connect to a MySQL Data Source in Visual Studio
  18. DOs and DONTs for Indexes [closed]
  19. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  20. How can I implement single table inheritance using Laravel’s Eloquent?
  21. How to insert images from database?
  22. MySQL – ORDER BY values within IN()
  23. How can I set autoincrement format to 0001 in MySQL?
  24. MySQL convert timediff output to day, hour, minute, second format
  25. How do I rename a MySQL database (change schema name)?
  26. Error installing mysqlclient for python on Ubuntu 18.04
  27. connecting to a docker-compose mysql container denies access but docker running same image does not
  28. MySQL’s lower_case_table_names won’t change
  29. mysql – move rows from one table to another
  30. #1214 – The used table type doesn’t support FULLTEXT indexes

Leave a Comment