How to select a value in the same table as the value for an update for each row.

Question

I have a table structure with columns like this

• [ID]
• [Name]
• [ParentId]
• [ParentName]

The parents are contained in the same table, and i would like to populate the parent name column using a statement like:

UPDATE Table
   SET ParentName = (select Name  
                      from Table 
                     where Id = ParentId)

When i do this, all the ParentNames are set to null. Thoughts?

Answer 1

Here , sub query returning null values, So that it is assigning null to ParentName

Answer 2

Here is a solution that I have working

UPDATE TABLE
SET ParentName = b.Name from
(
    select t.name as name, t.id as id
    from TABLE t  
) b
where b.id = parentid

Note I refuse to believe that it has to be this ugly, I'm sure that something very similar to what OMG Ponies posted should work but try as I might I couldn't make it happen.

Answer 3

Here's another T-SQL syntax you can use :

(BTW, I agree with cletus about the denormalization concerns.)

-- create dummy table
create table test (id int, name varchar(20), 
parentid int, parentname varchar(20))

go

-- add some rows 
insert test values (1, 'parent A', null, null)
insert test values (2, 'parent B', null, null)
insert test values (3, 'parent C', null, null)

insert test values (11, 'child A 1', 1, null)
insert test values (12, 'child A 2', 1, null)
insert test values (33, 'child C 1', 3, null)

go

-- perform update
update c set parentname = p.name from test c join test p on c.parentid = p.id 

go

-- check result
select * from test

Answer 4

I would go with the update from statement.

UPDATE tb
SET
    tb.ParentName = parent.Name
FROM Table tb
INNER JOIN Table parent ON parent.Id = tb.ParentId

This is T-SQL specific, but it should work pretty well.

Answer 5

UPDATE
    T
SET
    parentname = PT.name
FROM
    MyTable T
    JOIN
    MyTable PT ON t.parentid = PT.id

You error occurs becasue you have no correlation in the subquery. You get zero rows unless "Id = ParentId" in each row

select Name from Table where Id = ParentId -- = no rows

You can't use an alias like UPDATE TABLE T ... so push the JOIN/correlation into the FROM clause (or a CTE or derived table)