Delete query

15 pts.
Tags:
Access 2000
Database
DELETE statement
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].

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,085 pointsBadges:
    report
  • Dwaltr
    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.
    900 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following