I have not been programming for awhile and I can not figure out the delete. Have "DELETE FROM orders WHERE orderstodelete.key = orders.key", but that only deletes the orders that match the current orderstodelete.key.
>>I have a cust.dbf and an orders.dbf. I want to create some SQL to purge older records. If the customer has not ordered anything in the last two years, I want to delete all his orders and his customer record. There is a unique CUST_KEY field in cust.dbf and orders.dbf. orders.dbf also has SEQ and ADD_DT fields.
>>
>>Thanks
>>
>>Brenda
>
>
>select custID, OrderID from Orders ;
>inner join (select custID, max(Order_Date) as LastOrderDate ;
>from Orders group by 1 ;
>having LastOrderDate < date() - 365*2) LastOrder ;
>on Orders.CustID = LastOrder.CustID into cursor OrdersToDelete
>
>Proceed from there.
>
>Not tested.