Q
Problem solve Get help with specific problems with your technologies, process and projects.

Delete first row by date for each customer

Any idea on how I could delete every row/entry containing the customer's first purchase date from a customer purchases table?

Any idea on how I could delete every row/entry containing the customer's first purchase date from a customer purchases table?

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.

This was last published in October 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close