Post reply

Please help me understand the difference between these two statements

  • March 10, 2010 at 12:43 pm

    #74053

    Hi,

    I am writing a trigger in T-SQL against an application. Each update stmt returns VERY different results and I'm not clear on why.

    This statement updated ALL records in the database (incorrect):

    UPDATE workorder SET targstartdate = @ts, targcompdate = @tc from inserted WHERE workorder.wonum = (select (wonum) from inserted)

    This statement only updates the current record (correct):

    UPDATE workorder SET targstartdate = @ts, targcompdate = @tc from workorder WHERE workorder.wonum = (select (wonum) from inserted)

    What is the difference between using "from inserted" and "from actualtablename"?

    Thank you!

    Sharon

  • March 10, 2010 at 2:03 pm

    #1131647

    UPDATE workorder

    SET targstartdate = @ts, targcompdate = @tc

    from inserted

    WHERE workorder.wonum = inserted.wonum

    I'd advice you not to use subqueries. Ever.

    There is another way in almost every case.

    And that another way is always better.

    _____________
    Code for TallyGenerator

  • March 10, 2010 at 2:23 pm

    #1131653

    sharon macdonald (3/10/2010)

    Hi,

    I am writing a trigger in T-SQL against an application. Each update stmt returns VERY different results and I'm not clear on why.

    Starting with this query

    This statement updated ALL records in the database (incorrect):

    UPDATE workorder SET targstartdate = @ts, targcompdate = @tc from inserted WHERE workorder.wonum = (select (wonum) from inserted)

    In this query the WHERE clause will always evaluate to true for all records as the wonum's you are pulling are from the the ones in the inserted table. All of those will correctly satisfy the WHERE clause.

    Now this one:

    This statement only updates the current record (correct):

    UPDATE workorder SET targstartdate = @ts, targcompdate = @tc from workorder WHERE workorder.wonum = (select (wonum) from inserted)

    This one isolates the which records in the table workorder will be updated to just those in the inserted table, which is what you wanted.

    What is the difference between using "from inserted" and "from actualtablename"?

    Thank you!

    Sharon

    Hope this helps.

    Now, what I would have done:

    UPDATE workorder SET

    targstartdate = @ts,

    targcompdate = @tc

    from

    workorder wo

    inner join inserted ins

    on wo.wonum = ins.wonum;

  • March 10, 2010 at 2:29 pm

    #1131655

    Blimey! I was going to suggest it was because in query 1, the workorder table isn't in the FROM list, so the WHERE clause wouldn't work, leading to an unrestricted update - I'd half expect an error. Then I looked in BOL and lo and behold, all of the sample queries follow this pattern. Shows how little I know. Regardless, I'd always use the UPDATE...FROM syntax recommended here on SSC based on the experience of others:

    UPDATE w SET targstartdate = @ts, targcompdate = @tc

    FROM workorder w

    INNER JOIN inserted i ON i.wonum = w.wonum

    Cheers

    ChrisM BSc PhD MOGC

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • March 10, 2010 at 2:36 pm

    #1131657

    You guys ROCK!! Sooooo helpful! THANK YOU!

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

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