SQL UPDATE or DELETE if duplicate

Question

I'm trying to run 3 queries in my database :

UPDATE `table` SET `rubriq` = '77' WHERE `rubriq` = '61';
UPDATE `table` SET `rubriq` = '77' WHERE `rubriq` = '62';
UPDATE `table` SET `rubriq` = '77' WHERE `rubriq` = '63';

In table i have two columns abonne and rubriq. Abonne is primary key, and two are index.

If in my database I have for example :

 abonne  | rubriq  
  84     |   61  
  84     |   62 
  84     |   63

When I run my 3 queries, for the first there is no problem but for the second, i have an error : #1062 - Duplicate entry '84-77' for key 1

How can i do for run these 3 queries but, when there is an error like that, just remove the line ?

Thanks !

Answer 1

You'll have to manually delete the records that will cause a conflict:

UPDATE `table` SET rubriq = 77 WHERE rubriq = 61;

DELETE t62
FROM   `table` t77
  JOIN `table` t62 USING (abonne)
WHERE  t77.rubriq = 77
   AND t62.rubriq = 62;

UPDATE `table` SET rubriq = 77 WHERE rubriq = 62;

DELETE t63
FROM   `table` t77
  JOIN `table` t63 USING (abonne)
WHERE  t77.rubriq = 77
   AND t63.rubriq = 63;

UPDATE `table` SET rubriq = 77 WHERE rubriq = 63;

Answer 2

This might be the same thing as what eggyal is doing, but their version is too complicated for me to understand. This is what I did in a similar situation.

UPDATE OR IGNORE table SET rubriq = 77 WHERE rubriq = 61;
DELETE FROM table WHERE rubriq = 61;

UPDATE OR IGNORE table SET rubriq = 77 WHERE rubriq = 62;
DELETE FROM table WHERE rubriq = 62;

UPDATE OR IGNORE table SET rubriq = 77 WHERE rubriq = 63;
DELETE FROM table WHERE rubriq = 63;

Basically, if a duplicate exists, ignore it. Then after the updates, delete any rows that are left over.