CODEWITHSUNDEEP
mysqlsql
Kenta
Kenta

Reputation: 391

Delete Customer data in SQL that have not ordered since certain date

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?

Upvotes: 0

Views: 4764

Answers (1)

awiebe
awiebe

Reputation: 3846

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.

Upvotes: 1

Related Questions