MySQL: updating a row and deleting the original in case it becomes a duplicate

Question

I have a simple table made up of two columns: col_A and col_B.

The primary key is defined over both.

I need to update some rows and assign to col_A values that may generate duplicates, for example:

UPDATE `table` SET `col_A` = 66 WHERE `col_A` = 70

This statement sometimes yields a duplicate key error.

I don't want to simply ignore the error with UPDATE IGNORE, because then the rows that generate the error would remain unchanged. Instead, I want them to be deleted when they would conflict with another row after they have been updated

I'd like to write something like:

UPDATE `table` SET `col_A` = 66 WHERE `col_A` = 70 ON DUPLICATE KEY REPLACE

which unfortunately isn't legal in SQL, so I need help finding another way around. Also, I'm using PHP and could consider a hybrid solution (i.e. part query part php code), but keep in mind that I have to perform this updating operation many millions of times.

thanks for your attention,

Silvio

Reminder: UPDATE's syntax has problems with joins with the same table that is being updated

EDIT: sorry, the column name in the WHERE clause was wrong, now I fixed it

Answer 1

Are there any foreign keys referencing this table? If not then the following should do:

CREATE PROCEDURE `MyProcedure` (IN invarA INT, IN invarB INT)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
    DELETE FROM table WHERE col_B = invarB;
    IF ROW_COUNT() > 0 THEN
        INSERT INTO table (`col_A`, `col_B`) VALUES (invarA, invarB);
    END IF;
END

Example call:

CALL `MyProcedure`(66, 70)

Answer 2

Answer to revised question:

DELETE FROM
    table_A
USING
    table AS table_A
    JOIN table AS table_B ON
        table_A.col_B = table_B.col_B AND
        table_B.col_A = 70
WHERE
    table_A.col_A = 66

This gets rid of the rows that would cause problems. Then you issue your UPDATE query. Ideally you will do it all inside a transaction to avoid a situation where troublesome rows are re-inserted in between the two queries.