Update a table with a CTE and then Delete the record thats no longer valid

Question

After i update the record with the appropriate values in the update statement, i then am trying to delete the record that is no longer valid. my Code is as follows, I'm not sure how you would use the values generated in the CTE to delete the values in the table, any help would be greatly appreciated.

    WITH CTE AS
(
       SELECT C.CompressorId, C.CompressorSK, ROW_NUMBER() OVER(PARTITION BY C.Compressorid order by C.EffectiveDate) as RowNumber
         FROM dimCompressor C
         where CompressorId = 8
)

,CTE_MAX AS
(
       SELECT CompressorId, MAX(CTE.RowNumber) AS MaxRow
         FROM CTE
       GROUP BY CompressorId
)

,CTE_SK AS
(
       SELECT DISTINCT c.CompressorId, m1.MaxSK, m2.NextMaxSK
       --, M1.MaxSK, M2.NextMaxSK
         FROM CTE C
         JOIN CTE_MAX M ON C.CompressorId = M.CompressorId




         CROSS APPLY (
                                  SELECT C1.CompressorSK AS MaxSK
                                    FROM CTE C1
                                  WHERE C1.CompressorId = M.CompressorId
                                     AND C1.RowNumber = M.MaxRow
              ) M1
         CROSS APPLY (
                                  SELECT C2.CompressorSK AS NextMaxSK
                                    FROM CTE C2
                                  WHERE C2.CompressorId = M.CompressorId
                                     AND C2.RowNumber = M.MaxRow - 1
              ) M2


)


UPDATE C1
   SET C1.EffectiveDate = C2.EffectiveDate

  FROM CTE_SK S
  JOIN dimCompressor C1 
    ON S.MaxSK = C1.CompressorSK
  JOIN dimCompressor C2
    ON S.NextMaxSK = C2.CompressorSK


-- This is the part i need help with, i need to delete the row associated with the C2.COmpressorSK
delete  FROM CTE_SK S
  JOIN dimCompressor C1 
    ON S.MaxSK = C1.CompressorSK
  JOIN dimCompressor C2
    ON S.NextMaxSK = C2.CompressorSK

Answer 1

You can't use the CTE twice in that fashion, but instead, why not just use the OUTPUT clause to capture the information from the UPDATE statement and then use that in the DELETE statement. It will do the same thing without having to repeat the CTE query (which could be quite costly).