Solved Query to delete the oldest record by customer account number

[Number11]

Look for some help i need to have a query to delete the oldest record within the table by customer_ID

I have Customer_ID and then the field i want to use is the importing_Date

So need to delete oldest record for the same customer, however not all customers will have more than 1 record?

 

[ebs17]

The task would be clearer if you formulated: Delete all records for the customer except for the newest one. Then:

DELETE
FROM
   TableX AS T
WHERE
   T.importing_Date <
      (
         SELECT
            MAX(X.importing_Date)
         FROM
            TableX AS X
         WHERE
            X.Customer_ID = T.Customer_ID
      )

 

[MajP]

I often do this in a two step process since the top per group query is often not updateatable and cannot be used to delete. In this case it is.
Here is some data from Northwind

Query2 Query2

Order ID	Customer	Order Date
10835​	Alfreds Futterkiste	15-Feb-96​
10952​	Alfreds Futterkiste	15-Apr-96​
11011​	Alfreds Futterkiste	09-May-96​
10308​	Ana Trujillo Emparedados y helados	19-Oct-94​
10625​	Ana Trujillo Emparedados y helados	08-Sep-95​
10692​	Ana Trujillo Emparedados y helados	03-Nov-95​
10759​	Ana Trujillo Emparedados y helados	29-Dec-95​
10926​	Ana Trujillo Emparedados y helados	03-Apr-96​
10365​	Antonio Moreno Taquería	28-Dec-94​
10507​	Antonio Moreno Taquería	16-May-95​
10535​	Antonio Moreno Taquería	13-Jun-95​
10573​	Antonio Moreno Taquería	20-Jul-95​
10643​	Antonio Moreno Taquería	25-Sep-95​
10677​	Antonio Moreno Taquería	23-Oct-95​
10682​	Antonio Moreno Taquería	26-Oct-95​
10856​	Antonio Moreno Taquería	28-Feb-96​
10355​	Around the Horn	16-Dec-94​
10383​	Around the Horn	16-Jan-95​
10453​	Around the Horn	24-Mar-95​
10558​	Around the Horn	05-Jul-95​
10707​	Around the Horn	16-Nov-95​
10741​	Around the Horn	15-Dec-95​
10743​	Around the Horn	18-Dec-95​
10768​	Around the Horn	08-Jan-96​
10793​	Around the Horn	24-Jan-96​
10864​	Around the Horn	04-Mar-96​
10920​	Around the Horn	02-Apr-96​
10953​	Around the Horn	15-Apr-96​
11016​	Around the Horn	10-May-96​

If I want the oldest per group

SELECT Orders.CustomerID, Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderID) In (Select Top 1 OrderID from orders as B where b.CustomerID = Orders.[CustomerID] Order By B.RequiredDate, B.OrderID)))
ORDER BY Orders.CustomerID;

qryTopByCustomer qryTopByCustomer

Customer	Order ID	Order Date
Alfreds Futterkiste	10835​	15-Feb-96​
Ana Trujillo Emparedados y helados	10308​	19-Oct-94​
Antonio Moreno Taquería	10365​	28-Dec-94​
Around the Horn	10355​	16-Dec-94​
Berglunds snabbköp	10278​	12-Sep-94​

Now make a delete query using qryTopByCustomer

DELETE qryTopByCustomer.CustomerID
FROM qryTopByCustomer;

 

[arnelgp]

you can also use:

DELETE TABLE1.* 
   FROM TABLE1 
        WHERE importing_Date <> DMAX("importing_Date","TABLE1","customer_ID=" & [customer_ID])

which is very basic.

 

[Number11]

you can also use:

DELETE TABLE1.*
   FROM TABLE1
        WHERE importing_Date <> DMAX("importing_Date","TABLE1","customer_ID=" & [customer_ID])

which is very basic.

Thanks this does work if the customer doesnt have more than 1 entry if they just have 1 entry it throughs up error

 

[Minty]

Just as an aside, most developers never delete records, just mark them with an Archive date or flag and then exclude them from day-to-day processing/reporting.

Deleting data is a one-off action, there is no recovery unless you have a backup.
If you are having storage issues consider archiving the records into another storage database.

 

[arnelgp]

does all importing_date has "date" on it?

 

[isladogs]

The OP did state that he/she only wanted to delete the oldest record for each customer.

Unless I'm mistaken, only MajP's solution does that.
The code in posts #2 and #4 will (I believe) delete all except the newest record

 

[ebs17]

I had only offered a simplification of the task, and @Number11 did not object.
You can also use:

DELETE
FROM
   TableX AS T
WHERE
   T.importing_Date =
      (
         SELECT
            MIN(X.importing_Date)
         FROM
            TableX AS X
         WHERE
            X.Customer_ID = T.Customer_ID
      )
   AND
   T.importing_Date <
      (
         SELECT
            MAX(X.importing_Date)
         FROM
            TableX AS X
         WHERE
            X.Customer_ID = T.Customer_ID
      )

 

[isladogs]

Yes, I think the code in #9 does what was requested.
After checking @arnelgp's version, I thought it was 'correct' after all but also get the same syntax error mentioned by the OP

 

[arnelgp]

i don't Encounter any errors that you have?
the technique can be found in Allen Browne's site.
in fact the code is from Allen Browne.

 

[isladogs]

I should have stuck to my original comment in post #8!

The OP did state that he/she only wanted to delete the oldest record for each customer.

Unless I'm mistaken, only MajP's solution does that.
The code in posts #2 and #4 will (I believe) delete all except the newest record

Apologies! The datatype mismatch error was my own stupid mistake.

However, your code DOES remove ALL except the newest record for each Customer_ID which is not what was requested

 

[Gasman]

I thought we would be looking for Min() in any record check?

 

[Number11]

Using #2 works well thanks

 

[Gasman]

Using #2 works well thanks

Completely the opposite of what was asked?

 

[isladogs]

Ah well . . . sometimes it is better to interpret what the OP may have meant than answer it correctly!