SELECT & UPDATE statements resulting in different counts??

  • Goalie35

    SSC Eights!

    Points: 854

    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

  • Gail Shaw

    SSC Guru

    Points: 1004446

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

    SSC Eights!

    Points: 854

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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 4 (of 4 total)

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