Delete query
Can you help me solve the following problem. Whenever i have an order where the field Audit = True, i will have to delete all the previous orders having the Audit = True. And only when customerid = 121. With other words i have to delete the previous orders having Audit = true but not the last one.
My query is the following :
SELECT [orders].[orderid], [orders].
Software/Hardware used:
Software/Hardware used:
ASKED:
April 18, 2008 5:53 PM
UPDATED:
April 18, 2008 8:50 PM
Answer Wiki:
You'll want a delete statement along these line.
<pre>delete from Orders
where CustomerId = 121 and OrderID <> (select max(OrderId) from Orders where CustomerID = 121)</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Are you entering the data via a form? If so you can use the Before Insert event (in the Form Property Sheet) to delete all the data prior to inserting the new row. That eliminates the subquery, making your delete statement look like this:
delete from orders where audit=True and customerid = 121 (or Formname.CustomerID)
Using the formname.customerid will allow it to work for whatever customer id is on the form.