0

Say I have two tables. A customers table and a orders table. The customers table has a list of anyone that has ever placed an order, while the orders table has a list of every order ever made. These tables are connected on customer.ID = orders.CustomerID. Each customer has a unique ID.

The orders table also gives the Date of the order. I would like to delete all customer rows that have not ordered given a certain date.

The query I have so far is as follows:

DELETE * FROM customers join orders on customers.id = orders.CustomerID WHERE orders.Date <= 'Date Input form Form';

However, I fear that customers who have ordered both before and after the specified date will be removed as well, and I don't want that to happen.

The logic I'm looking for is more so like this:

delete customers where the orders.date is !> than the given date

Even then, will this logic protect customers who have ordered before the given date and after the given date?

What can I do to achieve this?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Kenta
  • 369
  • 1
  • 10
  • 30
  • this will delete any customer that has ordered before the given date. – Jonathan Van Dam Jan 23 '17 at 18:09
  • Yes, but if they have ONLY ordered before the given date, those are the ones I want to delete. @JonathanVanDam – Kenta Jan 23 '17 at 18:25
  • Use a sub query to find the max(orders.Date) for each customer and only remove the ones that are less than the date specified. http://stackoverflow.com/questions/4471277/mysql-delete-from-with-subquery-as-condition – Chris H Jan 23 '17 at 20:43

1 Answers1

1

Could you perhaps propose a query similar to this?

DELETE FROM customers WHERE customer.id NOT IN
(SELECT order.customer_id from orders where order.date > cutoff)

Where customers must have ordered something after cutoff to be kept.

awiebe
  • 3,758
  • 4
  • 22
  • 33