CODEWITHSUNDEEP
sql-servert-sqlmergesql-update
Alex
Alex

Reputation: 2959

How to merge two row and sum some columns in an UPDATE?

I work with SQL Server and I need to merge multiples rows only if they have the same value between them in two specific columns (Col_01 and Col_02 in my example). When I merge them I have to sum some columns (Col_03 and Col_04 in my example).

I think an example will be the more explicit. Here is my table simplified :

| ID  | Col_01 | Col_02 | Col_03 | Col_04  |
| 1   | ABC    | DEF    | 2      | 2       | 
| 2   | ABC    | DEF    | 1      | 0       | 
| 3   | DEF    | GHI    | 0      | 2       | 
| 4   | ABC    | GHI    | 1      | 0       | 
| 5   | JKL    | GHI    | 0      | 2       |

And here is what I want after my update :

| ID  | Col_01 | Col_02 | Col_03 | Col_04  | 
| 2   | ABC    | DEF    | 3      | 2       | 
| 3   | DEF    | GHI    | 0      | 2       | 
| 4   | ABC    | GHI    | 1      | 0       | 
| 5   | JKL    | GHI    | 0      | 2       |

I merged ID 1 and ID 2 because they had the same Col_01 and the same Col_02.

I tried a query like that 

SELECT MAX(ID), Col_01, Col_02, SUM(Col_03), SUM(Col_04)
FROM Table
GROUP BY Col_01, Col_02

I've got what the rows merged but I loose the not merged ones.

I don't know how to properly use it in an UPDATE query in order to merge the rows with the same (Col_01, Col_02) and keep the others. Can you help me to do this ?

Upvotes: 3

Views: 2717

Answers (2)

Andrei Shakh
Andrei Shakh

Reputation: 181

So you actually need to delete some rows from original table. Use MERGE statement for that:

MERGE Table tgt
USING (SELECT MAX(ID) as ID, Col_01, Col_02, SUM(Col_03) AS Col_03, SUM(Col_04) AS Col_04
       FROM Table
       GROUP BY Col_01, Col_02) src ON tgt.ID = srs.ID
WHEN MATCHED THEN
    UPDATE
    SET Col_03 = src.Col_03, Col_04 = src.Col_04
WHEN NOT MATCHED BY SOURCE THEN
    DELETE

Upvotes: 3

Jesuraja
Jesuraja

Reputation: 3844

Try this:

UPDATE  T SET 
        T.Col_03 = G.Col_03,
        T.Col_04 = G.Col_04
FROM    Table AS T INNER JOIN
        (SELECT   MAX(ID) AS ID, Col_01, Col_02, SUM(Col_03) AS Col_03, SUM(Col_04) AS Col_04
         FROM     Table
         GROUP BY Col_01, Col_02) AS G 
         ON G.ID = T.ID

Upvotes: 2

Related Questions