Update table using case when

Question

I have the following table test

Id     Code        ParentId
1      R            O
2      Y            O
3      P            O
4      O            NULL

I need to update test like that :

   Id     Code        ParentId
    1      R            4
    2      Y            4
    3      P            4
    4      O            NULL

I tried that but it doesn't work , any idea ?

update [dbo].[test]
set [ParentId] =
CASE WHEN [ParentId]='' THEN [Id]
else select top 1  [Id] from [dbo].[PNL] where ParentId=[Code]
End

I got the table test updated

      Id     Code        ParentId
        1      R          NULL
        2      Y          NULL
        3      P          NULL
        4      O          NULL

What does the [dbo].[PNL] table look like? What error do you get? If no error, what is the resulting data? — mjsqu
– mjsqu, Commented Apr 24, 2014 at 10:53
Usually 'root' records have no parent - the value of parentId would be null. Why do you want it to be a parent of itself? This makes some queries quite unsafe, as this would be unusual (so normal queries looking for the root would cause infinite recursion). — Clockwork-Muse
– Clockwork-Muse, Commented Apr 24, 2014 at 11:07
@Clockwork-Muse You are absolutely right , I update my question . — user3548593
– user3548593, Commented Apr 24, 2014 at 11:11

dean · Accepted Answer · 2014-04-24 11:07:08Z

1

With updates and deletes it is usually safer to first test the select:

    select t1.*,
    case when t1.parentid is null then t1.id 
    else (select top 1 t2.Id from #t t2 where t1.ParentId = t2.Code) end as new_parentid
    from #t t1

and then do the actual update using CTE:

with x as (
    select t1.*,
    case when t1.parentid is null then t1.id 
    else (select top 1 t2.Id from #t t2 where t1.ParentId = t2.Code) end as new_parentid
    from #t t1
)
update x
set parentid = new_parentid

answered Apr 24, 2014 at 11:07

dean

10.1k2 gold badges27 silver badges27 bronze badges

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

Comments

MatBailie · Accepted Answer · 2014-04-24 10:57:02Z

1

A direct fix is:
- Put the sub-query in ()
- Make sure to specify [test] in that sub-query

(I've had to guess whether ParentID or [code] come from [test].)

update [dbo].[test]
set [ParentId] =
CASE WHEN [ParentId]='' THEN [Id]
else (select top 1  [Id] from [dbo].[PNL] where ParentId=[test].[Code])
End

answered Apr 24, 2014 at 10:57

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Comments

Diego · Accepted Answer · 2014-04-24 11:19:31Z

1

If I understand correctly, the requirements are fairly simple:

If a row doesn't have a ParentId, leave it alone.
If a row has a ParentId matching a Code in the same table, then update the ParentId with the matching row's Id.

In such case, a simple INNER JOIN update should work:

UPDATE
  test
SET
  ParentId = PT.Id
FROM
  test T
  -- The INNER JOIN will automatically discard all rows without ParentId
  INNER JOIN
  test PT ON
    (PT.Code = T.ParentId)

answered Apr 24, 2014 at 11:19

Diego

7,5905 gold badges34 silver badges38 bronze badges

Collectives™ on Stack Overflow

Update table using case when

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related