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

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.