write an update query with a self join

Question

i have to create as a query this step:

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

this code check where the column c1 have c3 valued and then on loop for each c2 that match, valued its c3.

for example:

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

and then the result is:

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

i tried tieh CTE but i can t do it...can someone help me??

!! SOLVED!!

using post of gofr1

here the CTE explanation

--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

And what results you need to achieve? or the last result set is one you want? — gofr1
– gofr1, Commented Sep 2, 2016 at 11:01

gofr1 · Accepted Answer · 2016-09-02 12:08:26Z

1

Using 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]

Output is the same you provided.

edited Sep 2, 2016 at 12:08

answered Sep 2, 2016 at 11:03

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

elle0087 Over a year ago

hi, thank you VERY MUCH! i had done the same query but i used "t1.c3" instead of "'000' as c3". what s the difference??

gofr1 Over a year ago

My pleasure! There is a statement where t1.c3 is null so if you select t1.c3 there will be NULL, and you need 000.

elle0087 Over a year ago

mmmm, thank you again, but i m so little confused...for me CTE is still not clear...i hope to understand it early!

gofr1 Over a year ago

As for me - it took a looong time to understand recursive CTEs :) This and this articles were very helpful and of course experience and using this concept in your work.

elle0087 Over a year ago

wow thank s again! now i understood! here i posted the CTE explosion

neer · Accepted Answer · 2016-09-02 11:44:28Z

GO 16 will update 16 times.

UPDATE [t_loop]
SET c3 = '000'
WHERE
[t_loop].c3 IS NULL AND
EXISTS
(
    SELECT 1 FROM dbo.[t_loop] t
    WHERE 
        t.c1 = [t_loop].c2 AND
        t.c3 IS NOT NULL 
)
GO 16 -- SELECT COUNT(*) FROM [t_loop]

Result:

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

Other option:

DECLARE @Counter INT = 1
WHILE @Counter < (SELECT COUNT(*) FROM [t_loop])
BEGIN
    UPDATE [t_loop]
    SET c3 = '000'
    WHERE
    [t_loop].c3 IS NULL AND
    EXISTS
    (
        SELECT 1 FROM dbo.[t_loop] t
        WHERE 
            t.c1 = [t_loop].c2 AND
            t.c3 IS NOT NULL 
    )

    SET @Counter += 1
END

Collectives™ on Stack Overflow

write an update query with a self join

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related