Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: Problem with Delete Query and Duplicate Data in table

    We have a bit of a situation there is duplicate data in our table (legitimately because of the way our system writes out values) that we need to delete 1 row at a time. So for example, let's just say that someone went to a store and bought 2 items and when the cashier scans the items it comes up on two line items (one for each time it's scanned). It would be line items 1 and 2. Now let's say the person came back and returned one of the item. I would need to delete this from our sales data table because it's no longer a legit sale. If I were to do a query where I matched the returns table against the sales table (joining it only on like transaction ID, transaction date, item, quantity, price, etc, but no line item info to make it easy) it would take both transactions out of the sales table and would leave me with nothing for that item. Check out my picture attachment for a graphical view of what I'm talking about. I need to do a query to remove only one instance of that particular item within that sales. Right now, it's taking all instances it finds (in this case it would remove 2, which is incorrect). Is this even possible with a query so that it only takes what it finds 1:1 instead of 1:many?
    Attached Thumbnails Attached Thumbnails DB example.JPG  

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    You can construct a join with a group by and having clause, or a correlated sub-query, and use the MIN or MAX aggregate functions, depending on how your determine which row to return, to limit the result set to one row.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts