Post reply

Update query nightmare!

  • January 14, 2011 at 8:01 am

    #232218

    Ok this one has got me confused, hopefully an obvious answer that i've missed..

    I'm running an update query on a table which has 1 record per day per ID...

    Update query below...

    updateDAYS

    setCODE = s.code

    fromDays w left outer join #sects s

    on w.id = s.id

    and w.date between s.startdate and s.enddate

    The result of the update is fine for most records apart from the example below

    2 Lines of data from the #Sects table

    IDStart DateEnd dateCode

    10528762009-09-02 00:00:00.0002009-09-28 00:00:00.0002

    10528762009-09-28 00:00:00.0002009-11-23 00:00:00.0003

    The results after the update..

    IDSSateCode

    10528762009-09-27 00:00:00.0002

    **10528762009-09-28 00:00:00.0002

    10528762009-09-29 00:00:00.0003

    10528762009-09-30 00:00:00.0003

    ** this should have the code 3 not a 2? if the update runs in the correct order.

    Am i being stupid?

  • January 14, 2011 at 8:12 am

    #1273567

    Have you tried this? :

    and w.date >= s.startdate and w.date < s.enddate

  • January 14, 2011 at 8:36 am

    #1273591

    Thanks for the reply,

    Yep i've tried that and i get the same result, its almost like ignores the previous row as its just been updated?

    I've used this type of query a million times and never spoted this type of issue?

  • January 14, 2011 at 8:45 am

    #1273602

    Please post the ddl and data so I can have a better look...

    Instructions here :

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 14, 2011 at 9:21 am

    #1273624

    Thanks for your help, i hope all this works....

    -- this table is the one that needs updating.....

    CREATE TABLE [dbo].[test_days](

    [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

    [in_date] [datetime] NULL,

    [mhsection] [varchar](50) COLLATE Latin1_General_CI_AS NULL

    ) ON [PRIMARY]

    INSERT INTO [iPM_Info_Reporting].[dbo].[test_days]

    ([facility_id]

    ,[in_date]

    ,[mhsection])

    SELECT '1052876','Sep 27 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 28 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 29 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 30 2009 12:00AM','0'

    -- this table and data hold the info that should update the above table

    CREATE TABLE [dbo].[test_Sect](

    [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

    [Startdate] [datetime] NULL,

    [Enddate] [datetime] NULL,

    [Sectioncode] [varchar](20) COLLATE Latin1_General_CI_AS NULL

    ) ON [PRIMARY]

    INSERT INTO [iPM_Info_Reporting].[dbo].[test_Sect]

    ([facility_id]

    ,[Startdate]

    ,[Enddate]

    ,[Sectioncode])

    SELECT '1052876','Sep 2 2009 12:00AM','Sep 28 2009 12:00AM','2' UNION ALL

    SELECT '1052876','Sep 28 2009 12:00AM','Nov 23 2009 12:00AM','3'

    -- this is the update that doesnt work as expected?

    updatetest_days

    setmhsection = s.sectioncode

    fromtest_days w left outer join test_Sect s

    on w.facility_id = s.facility_id

    and w.in_date between s.startdate and s.enddate

    wheres.sectioncode is not null

    Sept 28 should be updated with a 3 not a 2? Shouldnt it?

  • January 14, 2011 at 9:32 am

    #1273630

    No , since Sep28 matches on both rows , the update order is arbitrary.


    Clear Sky SQL
    My Blog[/url]

  • January 14, 2011 at 9:43 am

    #1273641

    In my head the column is updated with a 2 initially then its overwritten with a 3 when the next line of data is pulled down from the update query!

    Try the code and see what you get?

    If i use seperate updates as seperate statements i get the desired result but i dont really wont to write a cursor or something to do this as its just an update statement.

    I'm happy to admit defeat on this if no one else has had this issue!

  • January 14, 2011 at 9:53 am

    #1273654

    No point in admitting defeat.

    I changed your where clause with mine and it gives the correct output for the sample data, tho I'm not sure it will for your actual data.

    USE tempdb

    GO

    IF OBJECT_ID('test_days','U') > 0

    DROP TABLE test_days

    IF OBJECT_ID('test_Sect','U') > 0

    DROP TABLE test_Sect

    GO

    CREATE TABLE [dbo].[test_days](

    [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

    [in_date] [datetime] NULL,

    [mhsection] [varchar](50) COLLATE Latin1_General_CI_AS NULL

    ) ON [PRIMARY]

    INSERT INTO [dbo].[test_days]

    ([facility_id]

    ,[in_date]

    ,[mhsection])

    SELECT '1052876','Sep 27 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 28 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 29 2009 12:00AM','0' UNION ALL

    SELECT '1052876','Sep 30 2009 12:00AM','0'

    -- this table and data hold the info that should update the above table

    CREATE TABLE [dbo].[test_Sect](

    [facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

    [Startdate] [datetime] NULL,

    [Enddate] [datetime] NULL,

    [Sectioncode] [varchar](20) COLLATE Latin1_General_CI_AS NULL

    ) ON [PRIMARY]

    INSERT INTO [dbo].[test_Sect]

    ([facility_id]

    ,[Startdate]

    ,[Enddate]

    ,[Sectioncode])

    SELECT '1052876','Sep 2 2009 12:00AM','Sep 28 2009 12:00AM','2' UNION ALL

    SELECT '1052876','Sep 28 2009 12:00AM','Nov 23 2009 12:00AM','3'

    -- this is the update that doesnt work as expected?

    update test_days

    set mhsection = s.Sectioncode

    from test_days w left outer join test_Sect s

    on w.facility_id = s.facility_id

    and w.in_date >= s.Startdate and in_date < s.Enddate

    where s.Sectioncode is not null

    SELECT * FROM test_days

    GO

    IF OBJECT_ID('test_days','U') > 0

    DROP TABLE test_days

    IF OBJECT_ID('test_Sect','U') > 0

    DROP TABLE test_Sect

    GO

  • January 14, 2011 at 9:59 am

    #1273660

    Yes it does work, cant beleive I missed the '=' in your original reply..

    Just out of curiosity why doesnt between work?

    Thanks again, i'll try this on the whole dataset as this is one example in a big update query.

    Cheers

  • January 14, 2011 at 10:05 am

    #1273665

    BETWEEN >>> where date >= and date <=

    my version >> where date >= and date <

    That slight variation excludes the very first MS of the next day correcting the join nightmare. However they are NOT equivalent... just very close.

  • January 14, 2011 at 3:09 pm

    #1273827

    christopher.hawkes (1/14/2011)

    Thanks for the reply,

    Yep i've tried that and i get the same result, its almost like ignores the previous row as its just been updated?

    I've used this type of query a million times and never spoted this type of issue?

    Correct. An UPDATE will only update a row once no matter how many other times it qualifies for an update.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 17, 2011 at 1:25 am

    #1274117

    Jeff,

    Thanks for the information about UPDATE queries only updating a row once, I never realised this but now everything makes sense.

    Looks like i'll be writting a loop and firing off seperate UPDATES to get this working 100%!

    Thanks to all members for your help with this one.

  • January 17, 2011 at 2:19 am

    #1274142

    christopher.hawkes (1/17/2011)

    Jeff,

    Thanks for the information about UPDATE queries only updating a row once, I never realised this but now everything makes sense.

    Looks like i'll be writting a loop and firing off seperate UPDATES to get this working 100%!

    Thanks to all members for your help with this one.

    So I'm guessing my query wasn't working for you after all?

  • January 17, 2011 at 2:31 am

    #1274144

    Well it did and it didnt? The only issue i discovered when i ran it against the full datset was the final record for each "Facility_ID" didnt get an update!!! The example data didnt cover the whole period hiding this issue.

    Which makes sense with the < end_date code.

    I've now written a cusor which goes line by line through the "Test Sect" table and fires an update against the "Test days" table.

    I have to say it does seem like a lot of code for an UPDATE query.

    Cheers

    Chris

  • January 17, 2011 at 5:40 am

    #1274204

    Well yes and no.

    Then what you need is to mash up the periods into a single row which will then cause a single update.

    Then you'll have a simple update statement that will way outrun the cursor version.

    can you send us a little more sample data which caused my vesion of the query to fail?

    Also how do you decide which code to use when you have more than 1 valid period?

Viewing 15 posts - 1 through 15 (of 16 total)

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