MySQL Script to delete certain rows from table

Question

I have a table with the following structure:

ID | FK_1 | FK_2 | MONTH | STATE

12  171     26      4   APPROVED
13  171     26      4   SUPER_APPROVED
14  56      32      4   APPROVED
15  45      34      4   SUPER_APPROVED
16  45      34      4   APPROVED
17  22      65      4   REJECTED

I need to remove duplicate rows so that there is only one row with each possible combination of FOREIGN_KEY_1, FOREIGN_KEY_2 and MONTH.

If there are two rows for that combination (such as row ids 12 & 13 or 15 & 16 above) I want to only keep the row that has the state SUPER_APPROVED.

After the script has been run I would expect the following data to be in the table (based on example data above):

ID | FK_1 | FK_2 | MONTH | STATE

13  171     26      4   SUPER_APPROVED
14  56      32      4   APPROVED
15  45      34      4   SUPER_APPROVED
17  22      65      4   REJECTED

Any help would be greatly appreciated

Have you tried anything thus far? Please show your efforts so we can show improvements to your approach — David Wilkins
– David Wilkins, Commented Mar 27, 2014 at 12:42
Thats why is better to store data as state in numeric values. Get Max() or Min() is much easier than filter rows by values. — MikkaRin
– MikkaRin, Commented Mar 27, 2014 at 12:49

Gordon Linoff · Accepted Answer · 2014-03-27 12:52:37Z

The idea is to generate a list of the rows you want to keep. Here is a query that does that:

      select FK_1, FK_2, MONTH,
             coalesce(min(case when state = 'SUPER_APPROVED' then id end),
                      min(id)
                     ) as id
      from table t
      group by FK_1, FK_2, MONTH;

Note that when there is 1 row for the combination, then the id for that row is in id. Otherwise, it follows your rules of preferring the SUPER_APPROVED row.

You can use this for a delete by using left outer join:

delete t
    from table t left outer join
         (select FK_1, FK_2, MONTH,
                 coalesce(min(case when state = 'SUPER_APPROVED' then id end),
                          min(id)
                         ) as id
          from table t
          group by FK_1, FK_2, MONTH
         ) as tokeep
         on t.id = tokeep.id
    where tokeep.id is null;

@PiX06 . . . table was just meant to be a generic place holder for your table name.

Collectives™ on Stack Overflow

MySQL Script to delete certain rows from table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related