• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1362
  • Last Modified:

Nesting a select in a delete

My problem is that whenever I use a nestedt select statement in my delete statment, and that select statement uses set arithmitic ie. union, minus, etc. I get an error (ORA-01732) stating that I cannot do this.  Is there some sort of easy work around without re-writing the scripts.
0
rproud
Asked:
rproud
  • 3
  • 2
  • 2
  • +2
1 Solution
 
FBIAGENTCommented:
pls post your query
0
 
Mark GeerlingsDatabase AdministratorCommented:
You may be able to create a view that contains the "union" and/or "minus" operator, then do a simple select from the view in your sub-query for the delete statement.
0
 
rproudAuthor Commented:
don't have access to it at the moment but it was along the lines of:

(select t1.* from t1, t2
where t1.blah = t2.blah(+))
minus
(select t1.* from t1, t2
where t1.blah = t2.blah)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Mark GeerlingsDatabase AdministratorCommented:
This should give you the same results (maybe faster) and avoid the "set" operator:
select * from t1 where not exists (select 1 from t2 where t2.blah = t1.blah)
0
 
rproudAuthor Commented:
ok i'll make sure i can nest that in the delete statement tomorrow and award points if it works
0
 
anand_2000vCommented:
can you post your entire delete statement?
0
 
rproudAuthor Commented:
delete from ((select t1.* from t1, t2
where t1.blah = t2.blah(+))
minus
(select t1.* from t1, t2
where t1.blah = t2.blah))
0
 
vc01778Commented:
Why not just:

delete from t1 where blah not in (select blah from t2);

or (as Mark suggested):

delete from t1 a where not exist (select 1  from t2 where blah=a.blah);


VC
0
 
vc01778Commented:
If,  for  whatever reason, you'd like to use  'minus',  you can do this:

delete from t1 where blah  in
(
select blah from
(
select * from t1
minus
select * from t2
)
)

VC
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now