Reputation: 1821
PK | PARENT | COMMENT
---+--------+-----------
1 | | I AM ONE
2 | | I AM TWO
3 | 1 | I AM ONE
4 | 1 |
5 | 2 | I AM TWO
6 | 2 | I AM THREE
I want to update the COMMENT
field of the rows where PARENT
is not null, so that the COMMENT
is the same as the parent. This is the expected result:
PK | PARENT | COMMENT
---+--------+---------
1 | | I AM ONE
2 | | I AM TWO
3 | 1 | I AM ONE
4 | 1 | I AM ONE
5 | 2 | I AM TWO
6 | 2 | I AM TWO
I've tried this query:
UPDATE MYTABLE
SET COMMENT =
(
select table2.COMMENT
from MYTABLE as table1
join MYTABLE as table2
on (table1.PARENT = table2.PK)
where table1.PARENT is not null
)
where PK in
(
select table1.PK
from MYTABLE as table1
left outer join MYTABLE as table2
on (table1.PARENT = table2.PK)
where table1.PARENT is not null
)
but this results in all the COMMENT
fields of rows with non-null PARENT
to be set to "I AM ONE".
Upvotes: 1
Views: 1136
Reputation: 180310
To update the rows where Parent
is not NULL, use a statement like this:
UPDATE MyTable
SET ...
WHERE Parent IS NOT NULL
To look up the parent's comment, simply use a correlated subquery (which needs to refer to the table in the UPDATE statement, otherwise it wouldn't know which row is the current row being updated):
UPDATE MyTable
SET Comment = (SELECT Comment
FROM MyTable AS MyParent
WHERE MyParent.PK = MyTable.Parent)
WHERE Parent IS NOT NULL
Upvotes: 6