SQL UPDATE or DELETE if duplicated

Question

I am 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 tableI have two columns abonneand rubriq. Abonne is the primary key, and two are the index.

If there is in my database, for example:

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

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

How can I do this to run these 3 queries, but when there is such an error, just delete the line?

Thanks!

Answer 1

You will have to manually delete entries that will lead to 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;