Post reply

SELECT & UPDATE statements resulting in different counts??

  • July 27, 2017 at 8:33 am

    #322905

    I'm trying to perform a simple update to a field, but many of the records I intended to update, were missed & I have no idea why.  In my SELECT statement below, I return 4,292 records.  These are the ones I want to update.  However, my UPDATE statement, only affects 584 records.  Am I missing something?  The joins & where clauses of each statement are exactly the same.  Where am I going wrong?

    SELECT statement:

    SELECT
    COUNT(*)
    FROM dbo.TableA cl
    INNER JOIN [dbo].TableB cf ON cl.[ClaimNumber] = cf.[CLAIM_ID]
    WHERE cl.client_program_number IS NULL
    and cf.client_program_number IS NOT NULL

    UPDATE statement:

    BEGIN TRANSACTION
    UPDATE cl
    SET cl.client_program_number = cf.client_program_number
    FROM dbo.TableA cl
    INNER JOIN [dbo].TableB cf ON cl.[ClaimNumber] = cf.[CLAIM_ID]
    WHERE cl.client_program_number IS NULL
    AND cf.client_program_number IS NOT NULL

    Thanks

  • July 27, 2017 at 8:49 am

    #1953046

    Multiple rows in TableB for each claim number in TableA?

    If that's the case, you may need to fix the update so that there's a 1-1 match, as it's not defined which of the matching rows of TableB will be used to update TableA

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 27, 2017 at 9:22 am

    #1953054

    Hi GilaMonster.

    That actually did turn out to be the culprit.  TableB contains multiple records for each claim number in TableA.  HOWEVER, all "client_program_number" values in TableB will be the same for all records with that claim number.  What would I need to change in my update to ensure a 1 to 1 match?  The claim # is the only common field between the 2 tables.
    Thanks again.

  • July 27, 2017 at 1:03 pm

    #1953092

    Goalie35 - Thursday, July 27, 2017 9:22 AM

     HOWEVER, all "client_program_number" values in TableB will be the same for all records with that claim number. 

    In that case, you don't need to worry. The update will give you the results you expect.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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