Delete rows with duplicate field values

Question

I have 3 tables: 'task', 'contact' and 'task_contact'. There's a BOOL column 'primary_contact' in 'task_contact' table. At first one task could have 1 or more primary contacts and 1 or more secondary contacts (optional) but now I decided to limit both to 1: so that 1 task can have only 1 primary and 1 secondary contact (optional). But I have an existing table 'task_contact' on the live-server which contains a lot of now inconsistent data.

What I need is to leave only the most recent rows with primary and the secondary (if it does exist) contacts in 'task_contact' table and delete all duplicated by 'primary_contact' rows so that I could apply the UNIQUE constraint for ('task_id', 'primary_contact').

Thanks in advance for any help.

Closed, op said "this solved it here:" http://stackoverflow.com/a/28266100/3709866 — Drew, Nov 07 '16 at 15:35

ScaisEdge · Answer 1 · 2016-11-07T14:35:33.557

1

Assuming that each task_contact row have a unique task_contact_id you could use

      delete from  
      task_contact_id 
      where task_contact_id  in ( select id from
        ( select max(task_contact_id) id, task_id
      from task_contact
      where primary_contat = false 
      group by task_id
      having count(*)>1
      ) t

If you have more then one then you can do the inverse

      delete from  
      task_contact_id 
      where task_contact_id  not in ( select id from
        ( select min(task_contact_id), task_id
      from task_contact
      where primary_contat = false 
      group by task_id
      having count(*) >1
      ) t
      and primary_contat = false

edited Nov 07 '16 at 14:35

answered Nov 07 '16 at 12:35

ScaisEdge

131,976
10
91
107

Thank you, but in case there're more than 2 duplcates (more than 2 rows with the same boolean value and 'task_id') the query deletes only one of duplicates and leaves all the others and works in this way for each task. Maybe you can quickly come up with a fix for this situation? – frostman Nov 07 '16 at 14:25
That is what I need is not to delete one of duplicates but to delete all duplicates and leave only the most recent one (with the biggest value of 'id'). – frostman Nov 07 '16 at 14:34
If i understand right you have more then one duplicate with primary_contat = false .. and you want preserver the last .. i have update the answer – ScaisEdge Nov 07 '16 at 14:37

Delete rows with duplicate field values

1 Answers1