select and delete rows within groups using mysql

Question

I've seen examples for duplicate row manipulation, but I can't figure out how to map them to solve my problem.

+----+------------+------+---------+
| id | date       | file | status  |
+----+------------+------+---------+
|  1 | 2011-12-01 |    1 | Pending |
|  2 | 2011-12-02 |    1 | Pending |
|  3 | 2011-12-03 |    1 | Done    |
|  4 | 2011-12-04 |    1 | Pending |
|  5 | 2011-12-05 |    1 | Done    |
|  6 | 2011-12-06 |    1 | Pending |
|  7 | 2011-12-07 |    1 | Pending |
|  8 | 2011-12-08 |    1 | Pending |
|  9 | 2011-12-09 |    2 | Pending |
| 10 | 2011-12-10 |    2 | Pending |
| 11 | 2011-12-11 |    3 | Pending |
| 12 | 2011-12-12 |    4 | Done    |
| 13 | 2011-12-13 |    5 | Pending |
| 14 | 2011-12-14 |    5 | Done    |
| 15 | 2011-12-15 |    5 | Pending |
+----+------------+------+---------+

For each file in the table:

I need to first select/delete any row where status='Pending', and its date is older than the youngest date for any row where status='Done'. For the example, this would select/delete rows with id 1, 2, 4, and 13.
I need to next select/delete any row where status='Pending' and it's not the oldest date where status='Pending'. For the example, this would select/delete rows with id 7, 8, and 10.

The resulting table is:

+----+------------+------+---------+
| id | date       | file | status  |
+----+------------+------+---------+
|  3 | 2011-12-03 |    1 | Done    |
|  5 | 2011-12-05 |    1 | Done    |
|  6 | 2011-12-06 |    1 | Pending |
|  9 | 2011-12-09 |    2 | Pending |
| 11 | 2011-12-11 |    3 | Pending |
| 12 | 2011-12-12 |    4 | Done    |
| 14 | 2011-12-14 |    5 | Done    |
| 15 | 2011-12-15 |    5 | Pending |
+----+------------+------+---------+

This will create and populate the test table in MySQL:

CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, date date DEFAULT NULL, file int(11) DEFAULT NULL, status varchar(45) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

INSERT INTO test VALUES (1,'2011-12-01',1,'Pending '),(2,'2011-12-02',1,'Pending '),(3,'2011-12-03',1,'Done'),(4,'2011-12-04',1,'Pending '),(5,'2011-12-05',1,'Done'),(6,'2011-12-06',1,'Pending '),(7,'2011-12-07',1,'Pending '),(8,'2011-12-08',1,'Pending '),(9,'2011-12-09',2,'Pending '),(10,'2011-12-10',2,'Pending '),(11,'2011-12-11',3,'Pending '),(12,'2011-12-12',4,'Done'),(13,'2011-12-13',5,'Pending '),(14,'2011-12-14',5,'Done'),(15,'2011-12-15',5,'Pending ');

Thanks to ziesemer for the correct SELECT queries--I learned a lot from them. Unfortunately, it appears that MySQL doesn't allow DELETE with a subquery, so I converted ziesemer's answer to use JOINS instead. But I'm a SQL noob, so please correct if these could be improved:

SELECT DISTINCT t1.* FROM test t1 INNER JOIN test t2
 WHERE t1.file = t2.file
   AND t1.status = 'Pending'
   AND t2.status = 'Done'
   AND t1.date < t2.date;

SELECT DISTINCT t1.* FROM test t1 INNER JOIN test t2
 WHERE t1.file = t2.file
   AND t1.status = 'Pending'
   AND t2.status = 'Pending'
   AND t1.date > t2.date;

To delete, replace the SELECT line with:

DELETE t1 FROM test t1 INNER JOIN test t2

ziesemer · Accepted Answer · 2011-12-24T04:58:30.983

1

I have these tested, working independently - though the 2nd must be executed after the 1st to get the results you provided in your example. I am having some difficulty getting them to work as one Select, as the 2nd query is dependent upon the state of the table after the 1st is complete...

Select *
    From my_table t1
    Where (status = 'Pending'
        And date < (
            Select Max(date)
                From my_table t2
                Where t2.file = t1.file
                    And t2.status = 'Done'));

Select *
    From my_table t1
    Where (status = 'Pending'
        And date > (
            Select Min(date)
                From my_table t2
                Where t2.file = t1.file
                    And t2.status = 'Pending'));

(I'll give a +1 to anyone else who's answer can do this in one query, while producing the same, accurate results - I'm stumped, for now.)

edited Dec 24 '11 at 04:58

answered Dec 24 '11 at 03:52

ziesemer

27,712
8
86
94

It doesn't need to a single select or delete, if it's not possible. But I'm having trouble turning these selects into deletes. Can you show me how to do that? I'm using 5.5.16. Thank you. – osoviejo Dec 24 '11 at 04:42
Just replace the first `Select * From` with `Delete From` (only on the outer query, not the inner query). – ziesemer Dec 24 '11 at 04:49
That's what I had tried: mysql> Delete -> From test t1 -> Where (status = 'Pending' -> And date < ( -> Select Max(date) -> From test t2 -> Where t2.file = t1.file -> And t2.status = 'Done')); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 Where (status = 'Pending' And date < ( Select Max(dat' at line 2 – osoviejo Dec 24 '11 at 04:56
I didn't actually test this on MySQL, but I was hoping what I wrote was portable. If you could provide a Create Table and Insert statements to recreate your test table with data to make this easier to test with in MySQL, I'll take another look at it tomorrow. – ziesemer Dec 24 '11 at 05:01
I think the issue was with trying to do a DELETE query that contains a subquery, which MySQL doesn't allow. I've added an answer with a conversion of your correct answer to use JOINs instead. – osoviejo Dec 24 '11 at 12:34
@osoviejo - An interesting find about the conversion to DELETE problem: http://bugs.mysql.com/bug.php?id=2920 – ziesemer Dec 24 '11 at 14:23

score 0 · Answer 2 · answered Dec 24 '11 at 03:48

Your first problem will be solved by the below query, just you need to to do a subselect:

delete from tables1 where (select * from table1 where status=pending AND file=1 AND date>2011-12-05)

for the other one it needs a little thinking. (let me think about it)

select and delete rows within groups using mysql

2 Answers2

Linked