UPDATE using self-referencing JOIN ? -- sql server programming

sql server programming : UPDATE using self-referencing JOIN ?

Itzik Ben-Gan
4/2/2005 9:22:57 AM

Specify the alias of the side you want to update instead of the original
table name:

UPDATE t1
SET Price = NULL
FROM test AS t1
INNER JOIN test AS t2
ON t1.Parent = t2.ID

--
BG, SQL Server MVP
www.SolidQualityLearning.com

[quoted text, click to view]

--CELKO--
4/2/2005 10:01:40 AM

Have you considered using SQL instead of dialect?

UPDATE Test
SET Price = NULL
WHERE EXISTS
(SELECT *
FROM Test AS T2
WHERE Test.parent_id = T2.node_id);

Better, use a nested set model for the hierarchy.

Axel Dahmen
4/2/2005 7:13:25 PM

Hi,

I'd like to update a self-referencing table like

CREATE TABLE test (ID INT PRIMARY KEY,Price SMALLMONEY,Parent INT REFERENCES
test (ID))
....
UPDATE test SET Price = NULL
FROM test AS t1 INNER JOIN test AS t2
ON t1.Parent = t2.ID

OK, this is not the actual statement, of course. But how can I define to use
t1.Price as update target?

I know there are alternatives to do this update. I just want to make sure
this is a design flaw and not me missing something.

TIA,
Axel Dahmen

Axel Dahmen
4/4/2005 9:58:38 PM

I did, but I was actually curious about the dialect version having some flaw
in its design.

Online Books isn't clear about the fact that a table alias can be used after
the UPDATE keyword. But Itzik helped me out here. Thanks a lot to him!

Can you enlighten me briefly on the term "nested set model"? You made me
curious... ;)

TIA,
Axel Dahmen

-----------------
"--CELKO--" <jcelko212@earthlink.net> schrieb im Newsbeitrag
news:1112464900.106186.105930@z14g2000cwz.googlegroups.com...
[quoted text, click to view]

Axel Dahmen
4/4/2005 10:01:54 PM

....ahh, forgot one:

Do you perhaps know about some case studies on the runtime behaviour of
those two different approaches (dialect vs. ANSI)? For me that would be the
only point preferring one version over the other.

Again, TIA,
Axel

--CELKO--
4/7/2005 12:22:24 PM

This is a simple example from Adam Machanic of some of the dialect
problems. Never use an alias in the UPDATE clause; it makes no sense
in the Standard SQL model, since the alas has to behave as if it were
materialized, used and then discard without ever touching the base
table.

CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);

CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);

You run this proprietary UPDATE with a FROM clause:

UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;

The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

You can goolge for the Nested Set model or buy my book TREES &
HIERARCHIES IN SQL for the code and painful details. The basic idea is
to use pairs of integers (lft, rgt) to define the range of subtrees --
think of XML tags or parentheses in algebra.

--CELKO--
4/7/2005 12:36:41 PM

[quoted text, click to view]
other. <<

What about correct answers? What about maintaining and porting the
code? Remember that 80% or more of the cost of a system is in
maintaining it, not coding it.