MySQL: updating a row and deleting the original in case it becomes a duplicate
- by Silvio Donnini
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_B` = 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_B` = 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
