3

I'm trying to delete data's from one table with join condition.

I tried this

Delete test_one from test_one Val 
                join test_two En  
                  on Val.Map_Fromphyid=En.Fromphyid And            
                     Val.Map_Tophyid=En.Tophyid     And 
                     Val.Map_Relname=En.Relname 
                  Where Val.Result='NOT Done'

But this is giving me this error

"SQL command not properly ended"

I know i can do that by this way 

Delete From test_one 
       Where Phyid In (Select Val.Phyid From test_one Val 
                                        join test_two En  
                                        on Val.Map_Fromphyid=En.Fromphyid And
                                           Val.Map_Tophyid=En.Tophyid And 
                                           Val.Map_Relname=En.Relname 
                                        Where Val.Result='NOT Done');

What is the problem with first method? if it is wrong how to acheive that thing without subquery?

I have seen this question . I am looking for query without any subquery in it. I found the answers only with subquery.

Community
  • 1
  • 1
Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
  • tl;dr the problem with the first method is that Oracle doesn't support it. – Ben Aug 17 '15 at 12:51
  • 1
    Hi guys, please flag for reopen, as the answer we get linked to doesn't show how to delete from a joined query in Oracle, so the question is only partly answered there. – Thorsten Kettner Aug 17 '15 at 13:21
  • Yes, it does @Thorsten - in fact it uses an almost identical method to the one the OP suggests. If it doesn't then there's thousands of duplicates of this particular issue and so why don't you suggest another one? P.S. I can insta re-open as well as close so if you think I'm wrong it would be simpler to ping me and explain why so I could do it instantly. – Ben Aug 17 '15 at 13:29
  • @Ben: No it doesn't. Both answers delete from a table not from a query, i.e. both do `delete from fact_tab` rather than `from ()`. – Thorsten Kettner Aug 17 '15 at 13:32
  • @Ben: With ping directly you mean to use the at sign, as I am doing right now? I didn't know you could open it easily again, btw. I thought it needed enough votes to re-open, just as it needs enough votes to close a request (at least that is how I always supposed it to work.) – Thorsten Kettner Aug 17 '15 at 13:43
  • You can't delete from a query in Oracle @thorsten; that's the entire point and what the accepted answer says (which is why I chose that one). You can @ ping people who Insta closed a question (as indicated by the gold badge next to my name in the close reason) - see http://meta.stackexchange.com/q/230865/179419 – Ben Aug 17 '15 at 13:59
  • @Ben: Well, you can. At least in Ora11g. So re-open please and I'll post an answer to show how. – Thorsten Kettner Aug 17 '15 at 14:08
  • Done @Thorsten; have fun. – Ben Aug 18 '15 at 15:15

2 Answers2

7

You are creating a view on the fly (i.e. instead of deleting from a table delete from t you want to delete from records you are getting from a query delete from <query>.

In order to select from such a temporary view you would need parantheses (e.g. select * from (select from b) rather than select * from select from b.

With an UPDATE statement it is just the same. So either delete from a table:

delete [from] test_one where ...

or from a view

delete [from] (select * from test_one ...)

but don't forget the parentheses then. And don't name both a table and a view as in 

delete [from] test_one [from] (select * from test_one ...)

which is what you are doing.

I find it preferrable to delete from a table directly when possible, as deleting from temporary views seems somewhat fuzzy to me in Oracle, which I consider less readable:

  • delete from (select * from a join b on a.x = b.x) deletes from a
  • delete from (select * from b join a on a.x = b.x) deletes from b
  • delete from (select a.* from b join a on a.x = b.x) deletes from a

This is at least what happens in my Oracle Version here which is 11g.

As to your DELETE statement, I would suggest to write it thus:

delete from test_one val 
where result = 'NOT Done'
and exists
(
  select *
  from test_two en  
  where en.fromphyid = val.map_fromphyid 
  and en.tophyid = val.map_tophyid     
  and en.relname = val.map_relname 
);

I consider this readable: delete all 'NOT Done' records from table test_one for which also exists an entry in table test_two.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
2

You can use Merge command in Oracle.

Merge into test_one val
Using test_two en
on (
    Val.Map_Fromphyid = En.Fromphyid And
    Val.Map_Tophyid   = En.Tophyid And
    Val.Map_Relname   = En.Relname  
)
WHEN MATCHED THEN
DELETE WHERE Val.Result = 'NOT Done';
Praveen
  • 8,945
  • 4
  • 31
  • 49
  • The poster is not clear about the relationship between test_one and test_two and I think your answer (while good) is assuming that test_one has a primary key consisting of the three columns in question. If that's not true, then the merge will fail with an ORA-30926 (unable to get a stable set of rows...) – Matthew McPeak Aug 17 '15 at 14:05