title: SQL Server update statement based on Select Statement with multiple values
body: <p>I have a Select Statement which works well, producing 7636 rows:    </p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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  
  )
  </code></pre>
  
  <p>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 =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.
  The statement has been terminated.</p>
  
  <p>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?</p>
  
  <p>Thank you in advance!</p>