时间戳

首页 > 解决方案 > 带有 LAG 的 SQL 更新

sql - 带有 LAG 的 SQL 更新

问题描述

我有以下更新，它根据先前的记录更新记录 -

    WITH CTE AS(
    SELECT 
        patient, 
        start,
        CASE 
        WHEN 
            ISNULL(start, '') = '' 
            AND cd = '3'
            AND LAG([thru_dt]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) BETWEEN CONVERT(VARCHAR, DATEADD(DAY, -30, thru_dt), 112) AND thru_dt
            AND LAG([cd]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) = '30'      
        THEN LAG([start]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) 

        WHEN ISNULL(hhstrtdt, '') = '' AND
            ROW_NUMBER() OVER(PARTITION BY patient ORDER BY cast(claimno AS int) DESC) = 1
            AND LAG([cd]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) = '30'     
            AND LAG([thru_dt]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) BETWEEN CONVERT(VARCHAR, DATEADD(DAY, -30, thru_dt), 112) AND thru_dt
        THEN LAG([start]) OVER (PARTITION BY [patient] ORDER BY cast(claimno AS int) ASC) 
        ELSE start
    END AS new_start
    FROM table
)
UPDATE CTE 
SET start = new_start

此查询一次只更新一条记录。例如，如果我有这个输入 -

start       patient     cd
20190307    497863693   30
NULL        497863693   30
NULL        497863693   30
NULL        497863693   30

要更新所有 3 行，第一行的值为 NULL start，我必须运行查询 3 次。

输出将是 -

start       patient     cd
20190307    497863693   30
20190307    497863693   30
20190307    497863693   30
20190307    497863693   30

有没有办法让查询更新上述患者的所有行，而不是一一进行？我可以保留表中 NULL 的计数，并在表中的 NULL 数量停止减少后使更新停止运行，但这似乎不是一个好方法。

标签： sqlsql-server

解决方案

我认为一次更新整个表的最简单方法是这样的

with rn_cte as (
    select * , row_number() over (partition by patient order by (select null)) rn
    from tTable)
update tTable
  set start=NULl
where rn>=2;

推荐阅读