This discussion is archived
3 Replies Latest reply: Dec 21, 2012 1:59 PM by orafad RSS

Correlated subquery in delete statement predicate does not work.

dgathman Newbie
Currently Being Moderated
On Oracle XE 10g 10.2.01, if a correlated subquery in the predicate of a delete statement uses a column in the correlated record to compare against a column from a view that contains a union, and a cross join, it causes the delete not to work (0 rows deleted). Test case:

CREATE TABLE A (ID NUMBER);

CREATE TABLE C (ID NUMBER);
ALTER TABLE C ADD CONSTRAINT XPKC PRIMARY KEY (ID);

CREATE TABLE B (ID NUMBER, C_ID NUMBER);
ALTER TABLE B ADD CONSTRAINT XPKB PRIMARY KEY (ID);
CREATE INDEX XIF1B ON B(C_ID ASC);
ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (C_ID) REFERENCES C (ID));

INSERT INTO A VALUES (1);
INSERT INTO C VALUES (1);
INSERT INTO B VALUES (1,1);

CREATE OR REPLACE FORCE VIEW ACL_VW (A_ID, C_ID) AS
SELECT A.ID, A.ID
FROM A
UNION
SELECT A.ID, C.ID
FROM C
CROSS JOIN A;

--This select works:
SELECT * FROM B
WHERE id = 1
AND EXISTS (SELECT 1 FROM ACL_VW WHERE C_ID = B.C_ID AND A_ID=1)

--A delete using the same predicate does not work (results in 0 rows deleted)
DELETE FROM B
WHERE id = 1
AND EXISTS (SELECT 1 FROM ACL_VW WHERE C_ID = B.C_ID AND A_ID=1)




This works correctly on other 10G Oracle editions with the latest patchset 10.2.0.5. Besides going to 11G XE is there anyway to update 10G XE to 10.2.0.5?

Thanks,
Don

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points