Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update output into Expand / Collapse
Author
Message
Posted Friday, May 24, 2013 5:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:39 PM
Points: 247, Visits: 738
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.
Post #1456387
Posted Friday, May 24, 2013 5:15 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:31 AM
Points: 557, Visits: 447
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.
Post #1456396
Posted Friday, May 24, 2013 5:26 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
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.

Post #1456403
Posted Friday, May 24, 2013 5:30 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:04 AM
Points: 23, Visits: 610
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"
Post #1456405
Posted Friday, May 24, 2013 8:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:39 PM
Points: 247, Visits: 738
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.
Post #1456549
Posted Friday, May 31, 2013 11:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:19 AM
Points: 36, Visits: 238
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
Post #1458812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse