SELECT ROW_NUMBER () OVER ( PARTITION BY REG_NO,MZ_FLAG,BL_TYPE ORDER BY BL_ORDER,BL_TIME ) CNO,BL_ORDNO,REG_NO,MZ_FLAG,BL_TYPE,BL_ORDER
FROM EMR_PATIENT_BL_NEW
where MZ_FLAG='#'
update EMR_PATIENT_BL_NEW b set b.BL_ORDER=(
with t as
(select ROW_NUMBER () OVER ( PARTITION BY REG_NO,MZ_FLAG,BL_TYPE ORDER BY BL_ORDER,BL_TIME ) CNO,BL_ORDNO
FROM EMR_PATIENT_BL_NEW t
where MZ_FLAG='#' )
select a.CNO from t a where a.BL_ORDNO=b.BL_ORDNO
)
update EMR_PATIENT_BL_NEW p1
set p1.BL_ORDER =
(select cno from (select ROW_NUMBER () OVER ( PARTITION BY REG_NO,MZ_FLAG,BL_TYPE ORDER BY BL_TIME ) CNO,BL_ORDNO
FROM EMR_PATIENT_BL_NEW t
where MZ_FLAG='#' AND DelFLAG ='F' and reg_no='202012090000022' ) V
where V.BL_ORDNO = p1.BL_ORDNO)
where 1=1 and exists(select * FROM EMR_PATIENT_BL_NEW where MZ_FLAG='#' AND DelFLAG ='F' and reg_no='202012090000022' and BL_ORDNO = p1.BL_ORDNO)
