CODEWITHSUNDEEP
mysql
Nathan
Nathan

Reputation: 143

MySQL: Delete records based on maximum value

I have a table containing two foreign keys. I need to delete all rows where key1 & key2 are the same, but val < $x.

key1 & key2 aren't distinct values; there can be multiple records with the same key1 and/or key1/key2 pair.

I've tried several approaches, but can't get anything to work. Every approach thus far results in a MySQL error (such as "can't reopen table") or an incorrect result.

Sample data from the table:

rownum  key1    key2    val    col    col2    col3    col4
1       123     1       2      a      b       c       d
2       123     1       2      e      f       g       h
3       123     2       3      i      j       k       l
4       123     2       3      m      n       o       p 
5       456     1       1      q      r       s       t

I need to delete all rows where "val" is < the highest "val" for any given key1/key2 pair.

In other words, for each distinct key1/key2 combo, I need to find the max "val" ($x), and delete any row where val is < $x.

Thus, the desired output after the delete statement is:

rownum  key1    key2    val    col    col2    col3    col4
3       123     2       3      i      j       k       l
4       123     2       3      m      n       o       p
5       456     1       1      q      r       s       t

(col-col4 are irrelevant in determining what records to delete, I included them only to note that the table includes additional columns.)

key1, key2, and "val" are all int type.

How can I delete rows where key1 & key2 are the same, but val < $x?

Upvotes: 3

Views: 172

Answers (2)

Robo
Robo

Reputation: 1032

I'm not sure what your primary key is so I'm just going to call it primary_key

First we need to find the max of key2 for each key1

SELECT key1 as fk1, max(key2) as max_key2 from table group by key1

Then all rows where key2 < max_key2

SELECT t.primary_key as id, s.fk1, s.max_key2, t.key1 from table as t, (SELECT key1 as fk1, max(key2) as max_key2 from table group by key1) as s WHERE t.key2 < s.max_key2 AND s.fk1 = t.key1

Then delete those rows

DELETE from table where primary_key in (SELECT id from (SELECT t.primary_key as id, s.fk1, s.max_key2, t.key1 from table as t, (SELECT key1 as fk1, max(key2) as max_key2 from table group by key1) as s WHERE t.key2 < s.max_key2 AND s.fk1 = t.key1))

I have not tested this but it's roughly how i would go about the problem.

It should go without saying but validate before you delete and have backups

Upvotes: 1

Shadow
Shadow

Reputation: 34294

Use a multi-table delete syntax in which you join the table on itself using the key fields:

DELETE t1 FROM table1 t1, table1 t2
WHERE t1.key1=t2.key1 AND t1.key2=t2.key2 AND t1.val < t2.val

Sqlfiddle - I modified the sample data to have different vals for key pairs.

Upvotes: 2

Related Questions