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
Last activity: Sep 23, 2024, 06:30 AM