Nested Coalescing possible in SQL?

First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should inherit attribute values from parent node.

for example, say I have the following table:

(nodeId int , color varchar, phone varchar) with two rows

5, "GREEN", "555-1212"
7, NULL, "777-5555"
8, NULL, NULL
9, "BLUE", NULL

in addition there is a tree structure that specifies that node 5 is the parent of node 7, and 7 is the parent of nodes 8 and 9. I know there is many ways to make trees in SQL but as a simple example let's say the tree is:

id, parentid

8, 7
9, 7
7, 5

Thus in this case, node 7 inherits the value "GREEN" from node 5 for attribute "color", but provides its own value "777-5555" for attribute "phone". Node 8, in turn, inherits "GREEN" for "color" from node 7
(really from node 5 since 7 did not specify its own) and "777-5555"
for "phone" from node 7. Node 9 provides its own value for "color" and inherits the one for "phone" from Node 7.

So the runtime values in the application are:

Node 5: "GREEN", "555-1212"
Node 7: "GREEN", "777-5555"
Node 8: "GREEN", "777-5555"
Node 9: "BLUE",  "777-5555"

Question 1: Is there a single SQL statement that for a given node can replace the NULLs with inherited values from the parent node?

Question 2: Is there a better way to structure such data in SQL as to make answer to question 1 possible?

I would restate the problem as follows:

In a nested structure child nodes inherit values from parent nodes _by reference_ or specify their own. "By reference" is the key word here. If it wasn't for that you could just duplicate the necessary values from the parent entitity upon creation.

Thanks!

• Jeff