UPDATE based on Aggregation - T/SQL

Question

CREATE TABLE #Table1 
(
  ID INT Identity (1,1), Col1 varchar(10), 
  Col2 DateTime2(7), Col3 INT, COl4 INT
);

INSERT INTO #Table1 VALUES
('Part1','2014-01-23 22:00:00.0000000', NULL, NULL),
('Part2','2014-01-23 23:00:00.0000000', NULL, NULL),
('Part3','2014-01-23 23:00:00.0000000', NULL, NULL),
('Part3','2014-01-23 23:30:00.0000000', NULL, NULL);

CREATE TABLE #Table2 
(
  ID INT Identity (1,1), C1 varchar(10), C2 varchar(10), 
  C3 bit, C4 Varchar(10), C5 DateTime2(7)
);

INSERT INTO #Table2 VALUES
('One',   'First',   1, 'Part1','2014-01-23 22:00:00.0000000'),
('Two',   'Second',  1, 'Part1','2014-01-23 22:00:00.0000000'),
('Three', 'Third',   0, 'Part1','2014-01-23 22:00:00.0000000'),
('Four',  'Fourth',  1, 'Part2','2014-01-23 23:00:00.0000000'),
('Five',  'Fifth',   0, 'Part2','2014-01-23 23:00:00.0000000'),
('Six',   'Sixth',   1, 'Part3','2014-01-23 23:00:00.0000000'),
('Seven', 'Seventh', 1, 'Part3','2014-01-23 23:00:00.0000000'),
('Eight', 'Eight',   0, 'Part3','2014-01-23 23:30:00.0000000');

I want to update #Table1 based on a match between #Table1 and #Table2 on this condition:

ON T1.Col1 = T2.C4 AND T1.Col2 = T2.C5

And aggregate T2.C3 values to update value for T1.Col3 and T1.Col4 where:

T1.Col3 = COUNT(T2.C3 WHERE T2.C3 = 1)
T1.Col4 = COUNT(T2.C3 WHERE T2.C3 = 0)

Expected contents of #Table1 after the update:

ID  Col1    Col2               Col3    Col4
--  -----   ----------------   ----   ----
1   Part1   2014-01-23 22:00   2      1
2   Part2   2014-01-23 23:00   1      1
3   Part3   2014-01-23 23:00   2      0
4   Part3   2014-01-23 23:30   0      1

My unsuccessful try at an update:

UPDATE T1
SET T1.Col3 = SUM(CASE WHEN T2.C3 = 1 THEN 1 ELSE 0 END)
,T1.Col4 = SUM(CASE WHEN T2.C3 = 0 THEN 1 ELSE 0 END)
FROM #Table1 T1 INNER JOIN #Table2 T2
    ON T1.Col1 = T2.C4 AND T1.Col2 = T2.C5

This fails with:

Msg 157, Level 15, State 1, Line 33
An aggregate may not appear in the set list of an UPDATE statement.

What version of SQL Server? Thank you very much for table structure, sample data, and desired results. — Aaron Bertrand, Jan 23 '14 at 22:10
On a *lot* of questions it is like pulling teeth trying to get these specifics. I asked about version just to tidy up the script a bit. — Aaron Bertrand, Jan 23 '14 at 22:23

score 2 · Accepted Answer · answered Jan 23 '14 at 22:10

2

You can't use aggregates in the UPDATE list like that (as the error message showed).

However, you can do it this way:

;WITH x(C4,C5,C3Yes,C3No) AS 
(
   SELECT C4, C5, 
    COUNT(CASE WHEN C3 = 1 THEN 1 END),
    COUNT(CASE WHEN C3 = 0 THEN 1 END) 
   FROM #Table2 GROUP BY C4, C5
)
UPDATE t1 SET Col3 = x.C3Yes, Col4 = x.C3No
FROM #Table1 AS t1
INNER JOIN x 
ON x.C4 = t1.Col1 AND x.C5 = t1.Col2;

answered Jan 23 '14 at 22:10

Aaron Bertrand

272,866
37
466
490

Ahh, this is a great workaround. Why did you use COUNT instead of SUM btw? – 007 Jan 23 '14 at 22:46
1

@user1569220 Simply because with `COUNT()` you don't have to say `ELSE 0`. It already only counts non-`NULL` values, and when all other conditions are false and you leave out `ELSE`, the result of a `CASE` expression is `NULL`. – Aaron Bertrand Jan 23 '14 at 22:49
Ahh, I saw the NULL and came back to post that...just to find you have already replied. As always, thanks for all your help Aaron. You are really good with T/SQL AND/OR Logical thinking. – 007 Jan 23 '14 at 22:51
watching your 10 query PASS video on YT right now. :) – 007 Mar 12 '14 at 22:02
Update, the NOT IN vs NOT EXISTS vs EXCEPT blew my mind...plus some great other tips. Thank you both. – 007 Mar 17 '14 at 19:42

UPDATE based on Aggregation - T/SQL

1 Answers1