CODEWITHSUNDEEP
sqlt-sql
abudker
abudker

Reputation: 712

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

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?

Upvotes: 4

Views: 8342

Answers (5)

gbn
gbn

Reputation: 432712

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)

Upvotes: 0

EndangeredMassa
EndangeredMassa

Reputation: 17528

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.

Upvotes: 5

Moe Sisko
Moe Sisko

Reputation: 12063

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

Upvotes: 4

David Hall
David Hall

Reputation: 33183

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.

Upvotes: 1

Pavunkumar
Pavunkumar

Reputation: 5345

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

Upvotes: 0

Related Questions