Sample Header Ad - 728x90

OUTPUT clause with window functions

7 votes
1 answer
548 views
**Is there an undocumented restriction in the OUTPUT clause, or is this a bug?** Given the following table:
CREATE TABLE t1 (SomeId int, flag bit, value int);
I would like to use a calculated value in an UPDATE statement, and then use OUTPUT to output the value. Note that the calculated value is not being used in the SET part, which might have allowed a workaround by outputting the left-hand-side column. The following works fine, it is a perfectly standard updatable subquery (derived table).
UPDATE subT1
SET flag = 1
OUTPUT inserted.SomeValue
FROM (
    SELECT *,
      SomeValue = t1.value + 123
    FROM t1
) subT1;
However using a window function gets a weird error:
UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT *,
      Sum = SUM(t1.value) OVER (PARTITION BY t1.SomeId)
    FROM t1
) subT1;
Msg 404 Level 16 State 1 Line 3
The column reference "inserted.Sum" is not allowed because it refers to a base table that is not being modified in this statement.
Which doesn't make sense as we already established that calculated columns can be referred to using inserted. OK let's try using OUTPUT subT1.Sum anyway, rather than inserted.Sum:
Msg 4104 Level 16 State 1 Line 3
The multi-part identifier "subT1.Sum" could not be bound.
which makes sense, since that table reference has been modified, and the docs say: > If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix. ____ Meanwhile, if I use a join on a second table:
CREATE TABLE t2 (SomeId int, flag bit, value int);
it works fine
UPDATE t1
SET flag = 1
OUTPUT subT2.Sum
FROM t1
JOIN (
    SELECT t2.*,
      Sum = SUM(t2.value) OVER (PARTITION BY t2.SomeId)
    FROM t2
) subT2 ON subT2.SomeId = t1.SomeId;
dbfiddle **So is the fact that a window function throws that error a bug, or an undocumented restriction?** Having said all that, a join *inside* a subquery is also not allowed, so most likely it is an undocumented restriction.
UPDATE subT1
SET flag = 1
OUTPUT inserted.Sum
FROM (
    SELECT t1.*,
      Sum = t2.value
    FROM t1
    JOIN t2 ON t2.SomeId = t1.SomeId
) subT1;
Msg 404 Level 16 State 1 Line 3
The column reference "inserted.Sum" is not allowed because it refers to a base table that is not being modified in this statement.
Asked by Charlieface (17545 rep)
May 7, 2023, 11:18 AM
Last activity: Sep 23, 2024, 06:30 AM