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!

Your results would suggest you have some duplicate `clientid` values in `#Client` — Nick, Feb 05 '20 at 22:27
Duplicate clientid values in #Client makes sense - Thank you @Nick! Still, how can i write a SQL query to update only the 7637 rows, based on the select statement? — Mo_Dlg, Feb 05 '20 at 22:36

score 0 · Answer 1 · answered Feb 05 '20 at 22:38

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

Understood @GMB, ToMigrate & OpenTreatmentEpisodes must be specified in the select. Executing the Select statement independently yields 7636 rows, running the WITH cte AS (SELECT ..) UPDATE cte Set ToMigrate = 1, OpenTreatmentEpisodes = 1 yields 7446 rows. If i change the "UPDATE cte Set ToMigrate = 1, OpenTreatmentEpisodes = 1" to "SELECT * FROM CTE", it yields 7636 rows.I understand the likelihood of duplicate clientid, so I ran this: "SELECT COUNT(*),clientid FROM #Client where OpenTreatmentEpisodes = 1 GROUP BY clientid HAVING COUNT(clientid) > 1" (7446 OpenTreatmentEpisodes=1), 0 rows. — Mo_Dlg, Feb 05 '20 at 23:12
PS - in simple words, the WITH cte script produces 7446 rows. — Mo_Dlg, Feb 06 '20 at 00:52

SQL Server update statement based on Select Statement with multiple values

1 Answers1