The immediate solution that comes to mind is:
delete from customer_purchases as t where purchase_date = ( select min(purchase_date) from customer_purchases where customer_pk = t.customer_pk )
If, for some strange reason, this doesn't work in your particular database system, then you may wish to separate the task into two steps. Some database systems will not let you delete from a table by specifying a subquery which references the same table. Can't imagine why.
The first step is to identify the rows that you want to delete, and save their primary keys into a temporary table:
insert into temp_purchases_to_delete select customer_purchase_pk from customer_purchases as t where purchase_date = ( select min(purchase_date) from customer_purchases where customer_pk = t.customer_pk )
The second step accomplishes the deletion:
delete from customer_purchases where customer_purchase_pk in ( select customer_purchase_pk from temp_purchases_to_delete )
As always, it's important to remember that before you run any SQL—especially SQL that you find on the Internet—which has the potential to alter or delete data, you should take a backup.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.