CODEWITHSUNDEEP
sqlms-accesssql-delete
Manu
Manu

Reputation: 11

How to convert this SELECT Statement into a DELETE statement?

I want to delete all records with a particular id and category except the one with the max date in MS Access.

Edit: This is how my Data looks like

id category date
1 1 24 June 2021
1 1 20 June 2021
1 2 25 June 2021
1 2 26 June 2021
2 1 24 June 2021
2 1 26 June 2021

And this is how i want my data to look like

id category date
1 1 24 June 2021
1 2 26 June 2021
2 1 26 June 2021

I have this SELECT statement to show me all records I want to keep:

Select
    t1.*
From
    table t1
inner join
    (select max(date) as maxdate, id
     from table
     group by id) t2 on t1.id = t2.id 
                     and t1.date = t2.maxdate

I can't figure out a Delete statement that works in Access.

Upvotes: 1

Views: 484

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use:

delete from t
   where t.date < (select max(t2.date)
                   from t as t2
                   where t2.id = t.id and t2.category = t.category
                  );

Upvotes: 1

Related Questions