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)
精彩评论