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

Question

I have a table structure with such columns

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

Parents are contained in the same table, and I would like to populate the column of the parent name using an operator, for example:

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

When I do this, all parent names will be set to zero. Thoughts?

Answer 1

Here is another T-SQL syntax you can use:

(By the way, I agree with cletus about denormalization issues.)

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

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 specific T-SQL, but it should work very well.

Answer 3

Here is the solution I'm working with

 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 should be ugly, I am sure that something very similar to what OMG Ponies published should work, but try as I could not do it.

Answer 4

Here's a helper query returning null values ​​so that it assigns null ParentName

Answer 5

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

You were mistaken because you have no correlation in the subquery. You get null rows if only "Id = ParentId" in each row

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

You cannot use an alias like UPDATE TABLE T ... , so press JOIN / correlation in the FROM clause (or CTE or view)