CODEWITHSUNDEEP
sqlsql-server
Hannover Fist
Hannover Fist

Reputation: 10860

SQL Update with Multiple updates per Record

I am trying to update months of a given persons records to reduce their potential if they get a claim within a month.

UPDATE M 
SET POTENTIAL_HITS = POTENTIAL_HITS - 1  
FROM #TEMP_CDC_MEMBERS M 
INNER JOIN #TEMP_CDC_CLAIMS C ON M.CIN = C.CIN AND C.MEASURE_INDICATOR = M.SUB_MEASURE
WHERE M.MOE > C.MOE

The MOE is a month field in a 201607 format.

The problem I am having is if there is a hit in one month, that difference will be ignored in a later month (due to SQL transactions).

Is there a way to update the potential without using a loop?

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your problem is that update doesn't do cumulative updates -- only one update per record in m. I think you can do what you want using cross apply:

UPDATE M 
SET POTENTIAL_HITS = POTENTIAL_HITS - c.cnt
FROM #TEMP_CDC_MEMBERS M CROSS APPLY
     (SELECT COUNT(*) as cnt
      FROM #TEMP_CDC_CLAIMS C 
      WHERE M.CIN = C.CIN AND C.MEASURE_INDICATOR = M.SUB_MEASURE AND M.MOE > C.MOE) c;

Upvotes: 1

Related Questions