11-11-07, 18:23 #1Registered User
- 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?
11-11-07, 21:14 #2Registered User
- 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.