SQL Server update statement based on Select Statement with multiple values

Question

I have a Select Statement which works well, producing 7636 rows:

SELECT c.ClientId
    FROM   dbo.tblTreatment e
      JOIN dbo.tblProgramAssessment pa
    ON pa.TreatmentID = e.TreatmentId
      JOIN #Client c
    ON c.ClientId = e.ClientId
      LEFT JOIN dbo.tblCessationOfTreatment ct
    ON ct.TreatmentId = e.TreatmentId
      LEFT JOIN dbo.tblClientGP m
    ON m.ClientId = c.ClientId
    WHERE  e.IsOpen = 1 
    AND    e.IsDeleted = 0 
    AND    ct.CessationDate is null
    AND    c.IsDeceased = 0 

I'm trying to update these 7636 rows, but it updates 7446 rows instead. In my (limited) understanding, this is likely due to clientid's somehow. Here's the update statement:

UPDATE 
    #Client
SET 
    ToMigrate = 1
    , OpenTreatmentEpisodes = 1
WHERE 
    clientid in
    (SELECT c.ClientId
    FROM   dbo.tblTreatment e
      JOIN dbo.tblProgramAssessment pa
    ON pa.TreatmentID = e.TreatmentId
      JOIN #Client c
    ON c.ClientId = e.ClientId
      LEFT JOIN dbo.tblCessationOfTreatment ct
    ON ct.TreatmentId = e.TreatmentId
      LEFT JOIN dbo.tblClientGP m
    ON m.ClientId = c.ClientId
    WHERE  e.IsOpen = 1 
    AND    e.IsDeleted = 0 
    AND    ct.CessationDate is null
    AND    c.IsDeceased = 0  
)

If I change the 'IN' to '=', I get an error message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

1) I don't really understand why there's difference in 7446 vs 7636 rows. 2) How can i update only the 7636 rows from the select statement?

Thank you in advance!

Answer 1

As commented by Nick, you likely have duplicated clientid in table #Client. As a result, some of the elements returned by your IN subquery match on several rows in the outer query, causing unwanted rows to be updated.

I think that you could work around this by leveraging the powers of the SQL Server updateable CTE. This works by turning your SELECT query to a CTE, and then UPDATE directly the CTE (note that for this to work, the CTE must return the columns that need to be updated).

WITH cte AS (
    SELECT c.ToMigrate, c.OpenTreatmentEpisodes
    FROM dbo.tblTreatment e
    INNER JOIN dbo.tblProgramAssessment pa ON pa.TreatmentID = e.TreatmentId
    INNER JOIN #Client c ON c.ClientId = e.ClientId
    LEFT JOIN dbo.tblCessationOfTreatment ct ON ct.TreatmentId = e.TreatmentId
    LEFT JOIN dbo.tblClientGP m ON m.ClientId = c.ClientId
    WHERE  
           e.IsOpen = 1 
           AND e.IsDeleted = 0 
           AND ct.CessationDate is null
           AND c.IsDeceased = 0 
)
UPDATE cte SET ToMigrate = 1, OpenTreatmentEpisodes = 1