Re: Simple Delete Gets Complex (What if delete becomes modify???)
Actually, Mark D Stock quoted me when he said...
>From: Kevin Beyer <beyer@cs.wisc.edu>
>Date: Fri, 18 Jul 1997 14:58:15 -0500
>X-Informix-List-Id: <news.40613>
>
>Mark D. Stock wrote:
>: Or do you need to do a full correlated subquery:
>:
>: DELETE FROM Table_A
>: WHERE Table_A.PK IN
>: (SELECT TA.PK
>: FROM Table_A TA, Table_B TB
>: WHERE TA.PK = TB.FK
>: AND TA.PK = Table_A.PK
>: AND ...
>: );
>
>Why would you need a correlated subquery like this? It seems to
>me that Table_A.* = TA.* because of the condition TA.PK = Table_A.PK.
>Am I missing something?
The intention of my question was to get the original asker to clarify what
they were trying to do -- I still don't understand exactly what was really
required.
As the next paragraph of the quoted message points out, you can't reference
the target table of the DELETE in the FROM clause of the sub-query anyway.
Also, and much more seriously, why are rows in TableA with an FK entry in
TableB being deleted? A referential integrity constraint would prevent
this from happening anyway.
To get back to your detailed question...
Let's make this concrete:
CREATE TABLE TableA
(
PK INTEGER NOT NULL,
Col1 CHAR(30) NOT NULL
);
INSERT INTO TableA VALUES(1, "Absolom");
INSERT INTO TableA VALUES(2, "Aardvaark");
INSERT INTO TableA VALUES(3, "Andalusian Abelone");
CREATE TABLE TableB
(
FK INTEGER NOT NULL,
Aux CHAR(25) NOT NULL
);
INSERT INTO TableB VALUES(1, "ABC");
INSERT INTO TableB VALUES(3, "GHI");
INSERT INTO TableB VALUES(4, "DEF");
DELETE FROM TableA WHERE PK = (SELECT FK FROM TableB WHERE FK = PK);
This DELETE statement runs and deletes rows with PK equal to 1 and 3 from
TableA. The reference to PK in the sub-query is a legitimate reference to
the table from which things are being deleted.
Given this query, there is no need to mention TableA in the sub-query.
However, it would be possible to require a second reference to TableA,
as in the sub-query:
DELETE FROM TableA
WHERE PK = (SELECT FK FROM TableB TB, TableA TA
WHERE TB.FK = TableA.PK
AND TableA.PK > TA.PK
AND TA.Col1 != "Aardvaark");
This, however, generates error -360: Cannot modify table or view used in
subquery. Note that this query cannot be rewritten without referencing
TableA twice... Similarly, adding the missing constraints and data:
ALTER TABLE tablea
ADD CONSTRAINT (PRIMARY KEY (pk) CONSTRAINT pk_ta);
INSERT INTO tablea VALUES(4, "Zanzibar Zombie");
ALTER TABLE tableb
ADD CONSTRAINT (FOREIGN KEY (fk) REFERENCES tablea(pk) CONSTRAINT fk_tb);
And now the original delete fails with error 692: Key value for constraint
(johnl.pk_ta) is still being referenced.
To some extent, this is a smoke and mirrors discussion; the basic answer
to your question is "You probably don't need the full correlated sub-query"
because the example above is pretty artificial. And your basic point about
TA.* being the same as TableA.* is correct.
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>