CODEWITHSUNDEEP
mysqlsql-updateunique-constraint
Andrew Senner
Andrew Senner

Reputation: 2509

MySQL update, but delete on duplicate key (due to unique index)

I'm looking for some insight in regards to updating several rows in a database table that would, in turn, violate a unique index of the table. For some context:

I have a many-to-many join table that defines a relationship between two other tables. There's a unique index on this table that says that the child table foreign keys must be unique to a single parent table. For example:

=============================
| ID | Parent ID | Child ID |
-----------------------------
|  1 |         1 |        1 |
|  1 |         1 |        2 |
|  1 |         1 |        3 |
|  1 |         2 |        1 |
|  1 |         2 |        2 |
|  1 |         3 |        1 |
=============================

I have a requirement to deprecate some of the allowable Child ID values and map those, now deprecated IDs, to their non-deprecated values. (e.g: in the above example, if we deprecate 3 => 2, it would cause a unique index violation because there is already a relationship between Parent 1 and Child 2)

Question: Is there a way to handle the unique index violations and just simply delete the current row being updated?

I know there's an INSERT ON DUPLICATE KEY UPDATE clause in MySQL, but is there an UPDATE ON DUPLICATE KEY DELETE?. I think we could even accept deleting the row that's causing the unique index, before performing the update. (e.g: Something like UPDATE ON DUPLICATE KEY DELETE EXISTING ROW)

Just trying to avoid a super complex SQL migration that's more prone to errors if I can.

Thanks for any help or tips, let me know if you need more information and I will provide as much as I'm allowed to.

Update: Thanks to Bill's answer I was able to come up with this SQL procedure:

DELIMITER $$
CREATE PROCEDURE `migrate_deprecated_children`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;

    -- Insert new items, ignoring any that already exist
    INSERT IGNORE INTO JOIN_TABLE (PARENT_ID, CHILD_ID)
        SELECT PARENT_ID,
           (CASE
                WHEN CHILD_ID = 106 THEN 68
                WHEN CHILD_ID IN (109, 111, 124, 131) THEN 110
                WHEN CHILD_ID IN (113, 114) THEN 61
                WHEN CHILD_ID = 115 THEN 48
                WHEN CHILD_ID IN (118, 143, 169, 77, 86) THEN 3
                WHEN CHILD_ID = 121 THEN -1
                WHEN CHILD_ID = 127 THEN 102
                WHEN CHILD_ID IN (134, 80) THEN 173
                WHEN CHILD_ID = 136 THEN 50
                WHEN CHILD_ID = 145 THEN 56
                WHEN CHILD_ID = 146 THEN 0
                WHEN CHILD_ID IN (14, 15) THEN 37
                WHEN CHILD_ID = 54 THEN 94
                WHEN CHILD_ID IN (84, 99) THEN 13
                WHEN CHILD_ID = 87 THEN 83
                WHEN CHILD_ID = 91 THEN 96
                WHEN CHILD_ID = 98 THEN 30
                ELSE CHILD_ID
               END) as CHILD_ID
           FROM JOIN_TABLE;

    -- Delete all deprecated rows
    DELETE FROM JOIN_TABLE WHERE CHILD_ID IN (14,15,54,77,80,84,86,87,91,98,99,106,109,111,113,114,115,118,121,124,127,131,134,136,143,145,146,169);

    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END$$

DELIMITER ;

Thanks again!

Upvotes: 1

Views: 698

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

There's no single SQL statement supported by MySQL that does what you describe.

The simplest solution is this two-step process:

INSERT IGNORE INTO m2mtable (id, parent_id, child_id)
 SELECT id, parent_id, 2
 WHERE child_id = 3;

DELETE FROM m2mtable WHERE child_id = 3;

You should do both statements wrapped in a transaction so the change appears to be atomic to any other client.

Not every task must be done in a single SQL statement.

Upvotes: 2

Related Questions