UPDATE not updating data in table

  • I'm trying to figure out why an update statement is failing to update.

    If I do a SELECT I get the following

    select locn.locnnbr,lav.AttribCd, locn.OccupancyCountBedYN , v.Descript

    from locn

    JOIN dbo.LocnAttribVal lav on lav.LocnNbr = locn.LocnNbr

    JOIN AttribVal v on v.AttribValNbr = lav.AttribValNbr

    where lav.AttribCd = 'BDCB'

    locnnbrAttribCdOccupancyCountBedYNDescript

    960 BDCB N Y

    961 BDCB N Y

    962 BDCB N Y

    963 BDCB N Y

    964 BDCB N Y

    There are over 9500 rows. This is a sample of the data

    The UPDATE looks like

    UPDATE l

    SET OccupancyCountBedYN = v.Descript

    from locn l

    JOIN dbo.LocnAttribVal lav on lav.LocnNbr = l.LocnNbr

    JOIN AttribVal v on v.AttribValNbr = lav.AttribValNbr

    where lav.AttribCd = 'BDCB'

    So this OccupancyCountBedYN should be 'Y' after the update

    I get the following when I execute the update

    (9547 row(s) affected)

    But yet all my rows are still 'N'

    Any ideas?

  • There's a Begin Tran and Rollback in the code somewhere.

    Without explicitly trying the code, no ddl to work with, it looks like it should work.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • There might also be a hidden update trigger on the table affecting the results?

  • joepacelli (3/19/2014)


    The UPDATE looks like

    You wouldn't believe how often folks have been caught out by omitting a seemingly inocuous bit of code from a query, only to find it's the part which is causing the problem. Can you post the whole statement please?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • It turned out to be a hidden trigger that wasn't disabled during the upgrade process.

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

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