Can I selectively update rows based on existence of duplicate rows?

Question

Database: MySql 5.6.x

I have a table that I want to update rows in two different ways.

╔════╦════════════╦══════╦════════╗
║ ID ║ NAME       ║ TYPE ║ STATUS ║
╠════╬════════════╬══════╬════════╣
║ 1  ║ fred       ║ A    ║        ║
║ 2  ║ barney     ║ B    ║        ║
║ 3  ║ wilma      ║ B    ║        ║
║ 4  ║ fred       ║ B    ║        ║
║ 5  ║ fred       ║ C    ║        ║
║ 6  ║ Betty      ║ A    ║        ║
╚════╩════════════╩══════╩════════╝

There is a unique constraint on the table (name, type)

I want to update the non-duplicating 'fred' records with name='barney' and soft delete (update status column) any records that would be duplicates.

╔════╦════════════╦══════╦════════╗
║ ID ║ NAME       ║ TYPE ║ STATUS ║
╠════╬════════════╬══════╬════════╣
║ 1  ║ barney     ║ A    ║        ║
║ 2  ║ barney     ║ B    ║        ║
║ 3  ║ wilma      ║ B    ║        ║
║ 4  ║ fred       ║ B    ║  DEL   ║
║ 5  ║ barney     ║ C    ║        ║
║ 6  ║ Betty      ║ A    ║        ║
╚════╩════════════╩══════╩════════╝

Can this be done in a single sql statement?

Answer 1

You can use the following UPDATE statement:

UPDATE mytable t1
LEFT JOIN mytable t2 ON t1.type = t2.type AND t2.name = 'barney'
SET t1.name = CASE 
              WHEN t2.name IS NULL THEN 'barney'
              ELSE t1.name
           END,
    t1.status = CASE 
                WHEN t2.name IS NULL THEN t2.status
                ELSE 'DEL'
             END
WHERE t1.name = 'fred';

The idea is to use a LEFT JOIN operation: a matching record indicates a duplicate had the UPDATE taken place.

Hence:

• If t2.name IS NULL there is no match and UPDATE can change name field. status remains unchanged in this case.
• If t2.name IS NOT NULL there is a match, so name remains unchanged and status gets updated to 'DEL'.

Demo here