What I've been asked to do is, if the combination of SOC_NUM & CLASS_NUM & EFF_DATE & ACCT_NUM & BEN_OPT_CD & TRANS_ID shows a value of "No Error" in the QError field, then find all records with that field combination and update the QError field to "No Error". So, in the sample table below, because at least one record with that field combination has a QError value of "No Error", all of them should.
SOC_NUM CLASS_NUM EFF_DATE SITUS STE_CD MED_JURIS_IND ACCT_NUM BEN_OPT_CD TRANS_ID BEN_EFF_DT Qerror Reportname
24674 145 1/1/2023 AL AL NJ 3209604 OAPIN 8992778 1/1/2022 SITUS_STATE_CODE_ERROR SITUS_STATE_ERROR_RPT
24674 145 1/1/2023 NJ NJ 3209604 OAPIN 8992778 1/1/2022 Missing STE_CD SITUS_STATE_ERROR_RPT
24674 145 1/1/2023 NJ AR NJ 3209604 OAPIN 8992778 1/1/2022 No Error SITUS_STATE_ERROR_RPT
24674 145 1/1/2023 NJ DE NJ 3209604 OAPIN 8992778 1/1/2022 No Error SITUS_STATE_ERROR_RPT
The table probably has 2 million records in it. I thought about a loop, but how would I know which record I paused at if I ran an Update command in the middle of it?
Here is one way to do it with window functions and an updatable CTE:
with cte as (
select Qerror,
max(case when Qerror = 'No Error' then 1 else 0 end)
over(partition by soc_num, class_num, eff_date, acct_num, ben_opt_cd, trans_id ) has_no_error
from mytable
)
update cte
set Qerror = 'No Error'
where has_no_error = 1 and Qerror <> 'No Error'
In the CTE, the window function checks if any row in the same group has value 'No Error' in column Qerror. With this information at hand, we can then update all related rows from CTE.
Note that the update ignores rows that already have the correct value, for efficiency. If Qerror may be null, the where clause should be adapted:
with cte as (...)
update cte
set Qerror = 'No Error'
where has_no_error = 1 and (Qerror is null or Qerror <> 'No Error')