SQL - Remove duplicates when other fields are equal

kez1304 · #1 (**permalink**) 02-06-12, 07:17

Hi guys,

Having a spot of bother writing this in SQL. I can achieve the results I'm after using VBA, but it will run much slower than a suitable SQL statement.

I have a table that looks like this:

Code:

Table  - tmp_orders_messy
Fields - Order Number
       - Product vName
       - Quantity

In this table, I want to do the following:

1) Find all the duplicate [Product vName]s, ONLY when they're associated with the same [Order Number].
2) Once found, I want to sum the [Quantity] fields for those values.
3) Then I want to either:
a) Delete all the duplicate records found, and create a new one with the same details, except for the modification of the [Quantity] field.
or...
b) Modify one of the duplicates to contain the summed [Quantity], and delete all the other duplicates.

To better explain this, let me give you an example...

Code:

 Order Number | Product vName | Quantity 
--------------+---------------+----------
      900     |  Set  Square  |     2
      900     |   Blue  Hat   |     1
      900     |  Set  Square  |     4
      940     |   Blue  Hat   |     3
      940     |  Set  Square  |     1

Is the data held in tmp_orders_messy, and the SQL statement should reduce it to it:

Code:

 Order Number | Product vName | Quantity 
--------------+---------------+----------
      900     |  Set  Square  |     6
      900     |   Blue  Hat   |     1
      940     |   Blue  Hat   |     3
      940     |  Set  Square  |     1

Like I said, using a combination of loops, ifs and an array I can achieve this with VBA, but its pretty slow when its checking through 40,000+ records. I'm hoping that a fairly sophisticated SQL statement will be much faster.

Any ideas?

Thanks in advance guys.

Sinndho · #2 (**permalink**) 02-08-12, 04:32

This SELECT query will provide the expected result:

Code:

SELECT   Tmp_orders_messy.[Order Number], 
         Tmp_orders_messy.[Product vName], 
         Sum(Tmp_orders_messy.Quantity) AS Quantity
FROM     Tmp_orders_messy
GROUP BY Tmp_orders_messy.[Order Number], 
         Tmp_orders_messy.[Product vName];

You can use it as the SELECT part of an INSERT query if you want, but I don't see any reason why you could not keep the table as it is and use the query instead, specially if its a temporary table which the Tmp_ prefix seems to indicate.

Internet Services

Web Development

Digital Marketing

Consumer Tech