SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update output into


update output into

Author
Message
Tobar
Tobar
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 758
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 = 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.
Bhaskar.Shetty
Bhaskar.Shetty
SSC Eights!
SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)SSC Eights! (856 reputation)

Group: General Forum Members
Points: 856 Visits: 509
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.
T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 599
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.
marc.snoeys
marc.snoeys
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 881
Let's explain it a bit as well ...

OUTPUT Clause (Transact-SQL)


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"
Tobar
Tobar
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 758
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.
CodeMuddler
CodeMuddler
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 244
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search