我正在尝试为比“ 2020-10-12”新的记录更新LocationNumber值。更新的记录应该是那些LocationNumber不等于'2020-10-10'的记录。12日之后,LocationNumber值对于某些记录已变得不正确,因此我正在尝试对其进行修复。
编辑:记录必须已经存在于'2020-10-10'上,我不想更新该日期系统中没有的新记录。
这是我尝试过的选项:
update ReadHeader as z0
set locationnumber = z1.locationnumber
from ReadHeader as z1
where z1.identifier = z0.identifier
and z1.LocationNumber in(select LocationNumber from ReadHeader where ReadLogDate = '2020-10-10')
and z0.LocationNumber in(select LocationNumber from ReadHeader where ReadLogDate > '2020-10-12')
and z0.LocationNumber not in(select LocationNumber from ReadHeader where ReadLogDate = '2020-10-10')
update ReadHeader SET LocationNumber = (SELECT LocationNumber from ReadHeader where ReadLogDate = '2020-10-10')
where ReadLogDate > '2020-10-12'
and LocationNumber not in(select LocationNumber from ReadHeader where ReadLogDate = '2020-10-10')
样本数据,每天都有成千上万的唯一“标识符”每天都有新的读取,我只想修复那些在“ 2020-10-12”之后具有不正确的LocationNumbers的标识符
Identifier LocationNumber AcctNum ReadLogDate Read Value
T108468264 336086 94103494 '2020-10-10' 5
T108468264 336086 94103494 '2020-10-11' 8
T108468264 888888 94103494 '2020-10-12' 19
T108468264 888888 94103494 '2020-10-13' 25
T108468264 888888 94103494 '2020-10-14' 30
所需结果:
Identifier LocationNumber AcctNum ReadLogDate Read Value
T108468264 336086 94103494 '2020-10-10' 5
T108468264 336086 94103494 '2020-10-11' 8
T108468264 336086 94103494 '2020-10-12' 19
T108468264 336086 94103494 '2020-10-13' 25
T108468264 336086 94103494 '2020-10-14' 30
任何帮助表示赞赏!
嗯。。。使用可更新的CTE:
with toupdate as (
select rh.*,
max(case when ReadLogDate = '2020-10-12' then locationnumber end) over (partition by identifier) as locationnumber_20201012
from ReadHeader rh
)
update toupdate
set locationnumber = locationnumber_20201012
where readlogdate > '2020-10-12';
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句