Reputation: 251

Update data on table 1 based on data in table 2

TABLE1:

CODE     KEY      GROUPBY     VALUE      <-- COLUMN

CAT       1       'NULL'       500       <-- DATA
DOG       2       'NULL'       400
RAT       3       'NULL'       300
SUM       4        1,3          
NUT       5       'NULL'       200
SUM2      6        2,5

TABLE2:

CODE     GROUPBY
SUM        1
SUM        3
SUM2       2 
SUM2       5

I want to get the SUM of data using GROUPBY which is based on KEY.

TABLE2 is a split string of TABLE1 where I separate the groupby values because I will base it in their KEY

The result in TABLE1 should look like this:

CODE     KEY      GROUPBY     VALUE      <-- COLUMN

CAT       1       'NULL'       500       <-- DATA
DOG       2       'NULL'       400
RAT       3       'NULL'       300       
SUM       4        1,3         800       <-- result
NUT       5       'NULL'       200
SUM2      6        2,5         600       <-- result

Upvotes: 0

Answers (3)

PP006

Reputation: 709

Try this,

SELECT    A.CODE,
          A.CODEKEY,
          A.GROUPBY,
          CASE
            WHEN A.VALUE IS NULL THEN B.VALUE
            ELSE A.VALUE
          END AS VALUE
FROM      #YOUR_TABLE_1 A
LEFT JOIN (SELECT A.CODE,
                  Sum(B.VALUE) AS VALUE
           FROM   #YOUR_TABLE_2 A
           JOIN   #YOUR_TABLE_1 B ON A.GROUPBY = B.CODEKEY
           GROUP  BY A.CODE) B ON A.CODE = B.CODE

Upvotes: 0

Maulik Shah

Reputation: 402

SQL Fiddle

Schema details

create table table1
(code varchar(20),
 codekey integer,
 groupby varchar(20),
 value integer);

 create table table2
 (code varchar(20),
  groupby integer);

  insert into table1 values('CAT',1,NULL,500);
  insert into table1 values('DOG',2,NULL,400);
  insert into table1 values('RAT',3,NULL,300);
  insert into table1 values('SUM',4,'1,3',NULL);
  insert into table1 values('NUT',5,NULL,200);
  insert into table1 values('SUM2',6,'2,5',NULL);

  insert into table2 values('SUM',1);
  insert into table2 values('SUM',3);
  insert into table2 values('SUM2',2);
  insert into table2 values('SUM2',5);

Update Query

update table1
set table1.value=tsum.codesum
from table1 t1a,
(select t2.code, sum(t1b.value) codesum from table1 t1b, table2 t2 where t1b.codekey=t2.groupby group by t2.code) tsum
where t1a.groupby is not null
and t1a.code=tsum.code

Output

| code | codekey | groupby | value |
|------|---------|---------|-------|
|  CAT |       1 |  (null) |   500 |
|  DOG |       2 |  (null) |   400 |
|  RAT |       3 |  (null) |   300 |
|  SUM |       4 |     1,3 |   800 |
|  NUT |       5 |  (null) |   200 |
| SUM2 |       6 |     2,5 |   600 |

While it is easy to just display the desired output via select query, writing the update query isn't that easy. Update query in SQL Server does not allow to specify Table1 twice.

Upvotes: 0

gotqn

Reputation: 43626

Try this:

DECLARE @DataSourceOne TABLE
(
    [CODE] VARCHAR(4)
   ,[KEY] TINYINT
   ,[GROUPBY] VARCHAR(8)
   ,[VALUE] SMALLINT
);

INSERT INTO @DataSourceOne ([CODE], [KEY], [GROUPBY], [VALUE])
VALUES ('CAT', '1', NULL, '500')
      ,('DOG', '2', NULL, '400')
      ,('RAT', '3', NULL, '300')
      ,('SUM', '4', '1,3', '')
      ,('NUT', '5', NULL, '200')
      ,('SUM2', '6', '2,5', '');

DECLARE @DataSourceTwo TABLE
(
    [CODE] VARCHAR(4)
   ,[GROUPBY] VARCHAR(8)
);

INSERT INTO @DataSourceTwo ([CODE], [GROUPBY])
VALUES ('SUM', 1)
      ,('SUM', 3)
      ,('SUM2', 2)
      ,('SUM2', 5);

WITH DataSource ([CODE], [VALUE]) AS
(
    SELECT DST.[CODE]
          ,SUM([VALUE])
    FROM @DataSourceTwo DST
    INNER JOIN @DataSourceOne DSO
        ON DSO.[KEY] = DST.[GROUPBY]
    GROUP BY DST.[CODE]
)
SELECT DSO.[CODE]
      ,DSO.[KEY]
      ,DSO.[GROUPBY]
      ,ISNULL(DS.[VALUE], DSO.[VALUE]) AS [VALUE]
FROM @DataSourceOne DSO
LEFT JOIN DataSource DS
    ON DSO.[CODE] = DS.[CODE];

The idea is to calculate the SUM in the CTE and then join the results to the first table.

Upvotes: 1

Update data on table 1 based on data in table 2

Answers (3)

Related Questions