Writing SQL Query to query and update same table

Question

I am not very friendly with SQL so I need to write a SQL update/delete query on a table. The requirement is as below.

Table A:

Col1(PK)     Col2            Col3            Quantity

1                 Code1        Value1            5

2                 Code2       Value2             2

3                 Code1         Value1            3

4                 Code3         Value3            8

Considering above table, in which there are multiple rows with same value for Col2 and Col3. I want to write a query which will delete duplicate combination of Col2 and Col3 and sum the Quantity for the resulting record.

The result should be like this:

Col1(PK)     Col2            Col3            Quantity

1                 Code1        Value1            8

2                 Code2       Value2             2

4                 Code3         Value3            8

Answer 1

You will need to do this in two parts, and if you want to ensure the integrity of the data, the two parts should be wrapped in a transaction.

The first part updates the required rows' Quantity, the second deletes the now duplicated rows.

BEGIN TRANSACTION

UPDATE TableA
SET Quantity=upd.Quantity
FROM TableA a
INNER JOIN (
    SELECT MIN(Col1) AS Col1, SUM(Quantity)  AS Quantity
    FROM TableA
    GROUP BY Col2, Col3 
) upd
ON a.Col1 = upd.col1

;WITH DELETES
AS
(
  SELECT Col1,ROW_NUMBER() OVER (PARTITION BY Col2,Col3 ORDER BY Col1) rn
  FROM TableA
)
DELETE FROM TableA WHERE Col1 IN (
    SELECT Col1 FROM DELETES WHERE rn>1
)

COMMIT TRANSACTION

Live example: http://www.sqlfiddle.com/#!3/9efa9/7

(EDIT: Updated to fix issue noted in comments)

Answer 2

Use this:

  select *
    from (select *, sum(Quantity) over (partition by col2,col3) as Quantity
          from tableA
         ) t

Answer 3

One option would be to just SELECT the result set you want into a new table, and then drop the previous table:

CREATE TABLE A_new(Col1 INT PRIMARY KEY,
                   Col2 varchar(255),
                   Col3 varchar(255),
                   Quantity INT);

INSERT INTO A_new (Col1, Col2, Col3, Quantity)
SELECT MIN(Col1) AS Col1, Col2, Col3, SUM(Quantity) AS Quantity
FROM A
GROUP BY Col2, Col3

Next you can drop table A and rename A_new to A:

DROP TABLE A
sp_rename A_New, A