SQL Server Query: Updating a column based on an aggregate query (1 Viewer)

[Banana]

I am sure I'm missing something blindingly obvious but here it is:

I'm trying to create a recordset in a denormalized fashion for reporting. Part of the requirement is to update a temporary staging table with column containing aggregate results.

The general SQL is something like this:

CREATE TABLE #t(
   c1 nvarchar(50),
   c2 nvarchar(50),
   c3 nvarchar(50),
   a1 float,
   a2 float,
   a3 float);

INSERT INTO #t (c1, c2, c3)
   SELECT DISTINCT c1, c2, c3 FROM t;

... <some more stuff relating to staging the aggregate data>

WHILE @currPos < @LoopCount
BEGIN
  UPDATE #t
    SET a1 = CASE @currPos WHEN 1 THEN a.g ELSE a1 END,
    SET a2 = CASE @currPos WHEN 2 THEN a.g ELSE a2 END,
    SET a3 = CASE @currPos WHEN 3 THEN a.g ELSE a3 END
  FROM
    (SELECT c1, c2, c3, SUM(a) as g 
     FROM t 
     WHERE c4 = @currPos
     GROUP BY c1, c2, c3) AS a
  JOIN #t ON
    (#t.c1 = a.c1 AND #t.c2 = a.c2 AND #t.c3 = a.c3);
  SET @curPos = @curPos + 1;
END;

The trouble is that I get no results updated with the above. If I modify the JOIN predicate so only one column instead of several columns is joined, everything works fine (albeit not correctly), but I need it to be joined or where'd on several columns. I thought it was a () issue, and tried to fiddle with syntax to no avail. I also tried using WHERE clause instead of JOIN, with same results. I don't have a key for the temporary table and am concerned that even if I did create a key, it doesn't get past the fact that I have to group by non-key columns and match up the aggregate data to the same group of non-key columns.

Also, the numbers of denormalized columns need to hold aggregate data won't be known until run-time, though there is a upper limit due to the CASE WHEN statement contained in UPDATE statement (no dynamic SQL here, thank you).

Does anyone see how the JOIN clause (or maybe a equivalent WHERE clause) could be written to get aggregate data with the multiple columns matching?

 

[gemma-the-husky]

Banana

i dont really follow the substance of what you are doing, but i often find with aggregate queries that i am unable to append the output of an aggregate query directly into another table because i get the "operation must use an updateable query" error

you're not being caught in that way are you?

 

[Banana]

No, that's the purpose of using a subquery (aliased as table a) to process the aggregate and thus allow updating the output into a table.

As far as I know, the trouble is in the JOIN or WHERE clause when I try to equal several columns. If I reduce the criteria to only one column, I can see new but incorrect values in the new table. As soon as I add one more column, the values vanish back to zero.

 

[Banana]

Well, I figure out a way, though I do feel as if I'm bludgeoning a baby seal at this point:

a JOIN #t ON (#t.c1 + #t.c2 + #t.c3 = a.c1 + a.c2 + a.c3)

All values seems to get inserted, but I do worry that this isn't really an efficient way. (BTW, it's set so Null doesn't propagate in this SP)

Would love to know if there truly is a better way...