May 24, 2013 at 5:05 am
What I am trying to do is return a value that is not changed from the row of the table that was changed. I am trying to replace a select @v-2 = col clause. I think this looks like the example in the BOL. Any idea why it reports that it reports f.a can not be bound?
create table foo(a int, b varchar(10), c int)
create table bar(z int, y int)
insert into foo (a, b, c) select 7, 'Test', 11
insert into bar select 3, 21
insert into bar select 10, 24
insert into bar select 7, 11
declare @foo2 table (avalue int)
update foo
set b = 'this is'
OUTPUT f.a into @foo2
from foo f
inner join bar b
on f.c = b.y
select avalue from @foo2
<><
Livin' down on the cube farm. Left, left, then a right.
May 24, 2013 at 5:15 am
Try this...
update foo
set b = 'this is'
OUTPUT DELETED.a INTO @foo2
from foo f
inner join bar b
on f.c = b.y
DELETED.a will update previous value and INSERTED.a will update the new value in @foo2 table.
May 24, 2013 at 5:26 am
Use OUTPUT INSERTED.a into @foo2 If you want to get the inserted value.
Use OUTPUT DELETED.a into @foo2 If you want to get the value replaced by update statement.
May 24, 2013 at 5:30 am
Let's explain it a bit as well ...
column_name
Is an explicit column reference. Any reference to the table being modified must be correctly qualified by either the INSERTED or the DELETED prefix as appropriate, for example: INSERTED.column_name.
As you can read: you can only specify column-names if, and only if, they are qualified by INSRETED ( the new values ) or DELETED ( the old values ).
"Walking on water and developing software from a specification are easy if both are frozen." -- Edward V. Berard, "Life-Cycle Approaches"
May 24, 2013 at 8:48 am
I have read the documentation on it. I guess I tend to believe the examples more than the documentation (probably why I am frustrated so often as I have found them lacking at times). If you look at the section "E. Using OUTPUT INTO with from_table_name in an UPDATE statement"
The following example updates the ScrapReasonID column in the WorkOrder table for all
work orders with a specified ProductID and ScrapReasonID. The OUTPUT INTO clause returns values
from the table being updated (WorkOrder) and also from the __Product table__ (emphasis mine). ...
USE AdventureWorks2012;
GO
DECLARE @MyTestVar table (
OldScrapReasonID int NOT NULL,
NewScrapReasonID int NOT NULL,
WorkOrderID int NOT NULL,
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL);
UPDATE Production.WorkOrder
SET ScrapReasonID = 4
OUTPUT deleted.ScrapReasonID,
inserted.ScrapReasonID,
inserted.WorkOrderID,
inserted.ProductID,
p.Name -- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
INTO @MyTestVar
FROM Production.WorkOrder AS wo
INNER JOIN Production.Product AS p
ON wo.ProductID = p.ProductID
AND wo.ScrapReasonID= 16
AND p.ProductID = 733;
SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID,
ProductID, ProductName
FROM @MyTestVar;
GO
Anyway, enough time wasted on trying to get this to work.
Thanks for the feedback.
<><
Livin' down on the cube farm. Left, left, then a right.
May 31, 2013 at 11:19 am
You may have figured this out already, but in your last example the reason they are able to use p.Name in the output clause is because the table that p is an alias for (Production.Product) is not being directly updated by the UPDATE command, it is merely being joined on. The table that is being updated (Production.WorkOrder) is only capable of being referenced via the INSERTED or DELETED aliases.
The same holds true for your original example. You are performing an update with a join, and the table that is being updated (foo) can only be referenced via INSERTED or DELETED. You could, however include columns from the table you are joining on (bar). For example:
update foo
set b = 'this is'
--OUTPUT f.a into @foo2
OUTPUT b.y into @foo2
from foo f
inner join bar b
on f.c = b.y
If you want to OUTPUT column(s) from foo that is not being effected by the SET statement, you can use either INSERTED or DELETED to get its value, as the before/after update value for the column will be identical.
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply