SQLTeam.com | Weblogs | Forums

How to add values of a specific row from one table to another table

tsql

#1

Hello,

I am attempting to add values of a specific row from one table to another table. Then ignore the same row on the other table. I am using 3 tables and my query is:

SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.a = TABLE2.b
UNION ALL 
SELECT * FROM TABLE3 ON 

The result is:

CAT	Co1	Co2	Co3	Co4
AA	1	0	6	3    -- TABE1
BB	1	2	0	1    -- TABE1
CC	1	1	1	1    -- TABE1
DD	1	2	3	8    -- TABE2
EE	3	1	1	4    -- TABE3

I need to add values of row EE to row BB.

3	1	1	4 +   -- EE 
1	2	0	1     -- BB
----------------------------
4	3	1	5     -- new values of row BB

Ignore row EE in the result.
Here is the output:

CAT	Co1	Co2	Co3	Co4
AA	1	0	6	3
BB	4	3	1	5    -- The values of row BB have changed
CC	1	1	1	1
DD	1	2	3	8

Thank you very much.


#2
SELECT CAT, Co1 = SUM(Co1), , Co2 = SUM(Co2), , Co3 = SUM(Co3), , Co4 = SUM(Co4)
FROM
(
  SELECT  CAT, Co1, Co2, Co3, Co4
  FROM    TABLE1 INNER JOIN TABLE2 ON TABLE1.a = TABLE2.b
  UNION ALL 
  SELECT CAT = CASE WHEN CAT = 'EE' THEN 'BB' END, Co1, Co2, Co3, Co4 
  FROM    TABLE3
) T
GROUP BY CAT

#3

It works great. Thanks for your help. :smile: