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

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?

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.

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

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.

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

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)