Delete old orders query

[pdwalke4]

I want to write a query that would delete records from 3 linked tables

Customer has Orders that have Items

The Order table holds the date of the orders. I want to delete all customers that have not placed in order since a user entered date.

Not got off the ground on this one. Any help appreciated.

Badger

 

[neileg]

If you have proper relationships and have imposed referential integrity and cascade deletes, all you need to do is delete the customer.

Are you sure you want to do this, though? What if the customer comes back and you want to see the historical data? I would think about apply a flag to the customer record to show the customer is inactive. You can then reset the flag at a later date if the customer becomes active again.

 

[pdwalke4]

The idea would be to delete customers who havwen't ordered for say 2 years or so (the db isn't or me so just making sure has all the tools requested). The query needs to have a link to the Date field in the order table so as to say if the oldest date order is beyond a user defined limit it would delete the customer data. Which is also a slight problem as i need to to identify only if the last record for a user is is before a certain date. Would i be best doing this as two quries? One to identfy the last order date and the second as the delete query?

 

[neileg]

Yes. Use Max on the date field to get the latest order. Then use this to drive the delete query. Might even be worth considering giving the user to cancel the delete for individual customers, just in case!