update output into

  • 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.

  • 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.

  • 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.

  • 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"

  • 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.

  • 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply