Re: Nested Coalescing possible in SQL?
Date: 4 Jun 2004 17:35:43 -0700
Message-ID: <18c7b3c2.0406041635.147a645b_at_posting.google.com>
>> if an entity instance (one row) does not specify a value for one of
its field [sic]s it should inherit the values from its nearest parent
that does have a value for this field [sic]. <<
Rows are not records; fields are not columns; tables are not files. It drives me nuts to see people screw up the terms and therefore their mental model of how SQL works. Let's put this into a simplified nested set model which has more NULL-able columns than the payroll system of a major auto company:
CREATE TABLE Nodes
(node_id INTEGER NOT NULL PRIMARY KEY,
col_1 CHAR(5),
col_2 CHAR(5),
..);
CREATE TABLE Tree
(node_id INTEGER NOT NULL UNIQUE
REFERENCES Nodes(node_id),
lft INTEGER NOT NULL,
rgt INTEGER NOT NULL,
PRIMARY KEY (lft, rgt),
<< other tree constraints here >>
..);
Now update all the rows where any column has a NULL. You will have to run this update once for every level in the tree at most.
UPDATE Nodes
SET col_1
- COALESCE(Nodes.col_1, -- keep non-nul
(SELECT B.col_1 -- find parent
FROM Tree AS E
LEFT OUTER JOIN
Tree AS B
ON B.lft
- (SELECT MAX(lft) FROM Tree AS S WHERE E.lft > S.lft AND E.lft < S.rgt)), col_2
- COALESCE(Nodes.col_2, (SELECT B.col_2 FROM ..)),
WHERE (col_1 || col_2 || ..||col_n) IS NULL;
This probably scared you. The WHERE clause uses the fact that NULLs propagate; we don't care *which* column is NULL, so why look for useless details? That left outer join scalar subquery is how to get the immediate superiors (B= boss, E= employee) in a hierarchy. The COALESCE() will retain an existing non-NULL value. Received on Sat Jun 05 2004 - 02:35:43 CEST