CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mayur Jadhav
Mayur Jadhav

Reputation: 125

Writing SQL Query to query and update same table

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

Upvotes: 5

Views: 1218

Answers (5)

Jamiec
Jamiec

Reputation: 136074

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)

Upvotes: 2

Mark
Mark

Reputation: 2063

For the first Update step(Assume that Table A has the name of Table_1) :

Update Table_1 set Quantity = t.total 
from Table_1 As p inner join 
(select Min(Col1) as Col1,SUM(quantity) as total from Table_1 group by Col2,Col3) as t
on p.Col1=t.Col1

this will update each row that has more than 1 row with its SUM of quantity.

then you can delete the same row which its code2 has the same value with :

WITH CTE AS(
   SELECT 
       RN = ROW_NUMBER()OVER(PARTITION BY Col2,Col3 ORDER BY Col2,Col3)
   FROM Table_1
)
DELETE FROM CTE WHERE RN > 1;

Sorry, I thought that Col2 would always be the same with Col3. *I have edited my statements. If you got more than 1 row, it could Delete all, except the first row.

Upvotes: 1

Bala
Bala

Reputation: 704

Please execute the below query:

SELECT 
    Col2,
    Col3,
    SUM(Quantity) 
FROM table_1
GROUP BY
    Col2,
    Col3

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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

Upvotes: 2

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Use this:

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

Upvotes: 2

Related Questions