sql-server - 使用自连接编写更新查询

Question

我必须在这一步创建一个查询:

update t1
set c3='000'
from t_loop t1
join t_loop t2
  on t1.c2=t2.c1
where t1.c3 is null
  and t2.c3 is not null

while (@@rowcount>0)
    update t1
    set c3='000'
    from t_loop t1
    join t_loop t2
      on t1.c2=t2.c1
    where t1.c3 is null
      and t2.c3 is not null

此代码检查 c1 列的 c3 值所在的位置，然后对每个匹配的 c2 循环，为其 c3 赋值。

例如:

select *
into [t_loop]
from
(
    select 'v1'c1,'v2'c2,NULL c3 union all
    select 'v10','v9',NULL union all
    select 'v2','v3',NULL union all
    select 'v3','v2','000'union all
    select 'v4','v2',NULL union all
    select 'v5',NULL ,NULL union all
    select 'v6',NULL ,NULL union all
    select 'v7','v1',NULL union all
    select 'v8','v7',NULL union all
    select 'v9',NULL ,NULL union all
    select 'va','vb',NULL union all
    select 'vb','vc',NULL union all
    select 'vc','vb',NULL union all
    select 'vd',NULL ,NULL union all
    select 've',NULL ,NULL union all
    select 'vf','vb','000'
)t

然后结果是:

c1  c2  c3
v1  v2  000
v10 v9  NULL
v2  v3  000
v3  v2  000
v4  v2  000
v5  NULL    NULL
v6  NULL    NULL
v7  v1  000
v8  v7  000
v9  NULL    NULL
va  vb  NULL
vb  vc  NULL
vc  vb  NULL
vd  NULL    NULL
ve  NULL    NULL
vf  vb  000

我试过 CTE，但我做不到...有人可以帮助我吗？？

!!解决了!!

使用 gofr1 的帖子

这里是CTE解释

--CTE explosion

--query 1
    SELECT  t1.c1,
            t1.c2
    from t_loop t1
    join t_loop t2
      on t1.c2=t2.c1
    where t1.c3 is null
      and t2.c3 is not null
    union all
--query 2
    SELECT  t1.c1,
            t1.c2
    from t_loop t1
    join --cte -> replace cte with the first query (query 1)
    (
        SELECT  t1.c1,
                t1.c2
        from t_loop t1
        join t_loop t2
          on t1.c2=t2.c1
        where t1.c3 is null
          and t2.c3 is not null
    )
     t2
      on t1.c2=t2.c1
    where t1.c3 is null
    union all
--query 3
    SELECT  t1.c1,
            t1.c2
    from t_loop t1
    join --cte ->  replace cte with the second query (query 2)
    (
        SELECT  t1.c1,
                t1.c2
        from t_loop t1
        join 
        (
            SELECT  t1.c1,
                    t1.c2
            from t_loop t1
            join t_loop t2
              on t1.c2=t2.c1
            where t1.c3 is null
              and t2.c3 is not null
    )
     t2
      on t1.c2=t2.c1
    where t1.c3 is null
    ) t2
      on t1.c2=t2.c1
    where t1.c3 is null

Answer 1

使用 CTE:

;WITH cte AS (
    SELECT  t1.c1,
            t1.c2,
            '000' as c3
    from t_loop t1
    join t_loop t2
      on t1.c2=t2.c1
    where t1.c3 is null
      and t2.c3 is not null
    UNION ALL
    SELECT  t1.c1,
            t1.c2,
            '000' as c3
    from t_loop t1
    join cte t2
      on t1.c2=t2.c1
    where t1.c3 is null
      and t2.c3 is not null
)


UPDATE t
SET c3 = c.c3
FROM [dbo].[t_loop] t
INNER JOIN cte c
    ON t.c1 = c.c1
        and t.c2 = c.c2

SELECT *
FROM  [dbo].[t_loop]

输出与您提供的相同。