CODEWITHSUNDEEP
mysqlsql
PiX06
PiX06

Reputation: 431

MySQL Script to delete certain rows from table

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

Upvotes: 0

Views: 138

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

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;

Upvotes: 3

Related Questions