如何使用 Oracle 中的连接从一张表中删除数据？

How to Delete data from one table using joins in Oracle?

提问by Arun Palanisamy

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"

“SQL 命令未正确结束”

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.

我见过这个问题。我正在寻找没有任何子查询的查询。我只用子查询找到了答案。

回答by Thorsten Kettner

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

您正在动态创建视图（即，不是从表中删除，而是delete from t从查询中获取的记录中删除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.

为了从这样的临时视图中进行选择，您需要括号（例如，select * from (select from b)而不是select * from select from b.

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

对于 UPDATE 语句，它是一样的。所以要么从表中删除：

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 anda 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:

我发现最好在可能的情况下直接从表中删除，因为在 Oracle 中从临时视图中删除对我来说似乎有些模糊，我认为它的可读性较差：

• 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

• delete from (select * from a join b on a.x = b.x)从一个删除
• delete from (select * from b join a on a.x = b.x)从 b 中删除
• delete from (select a.* from b join a on a.x = b.x)从一个删除

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

这至少是我的 Oracle 版本 11g 中发生的情况。

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

至于你的 DELETE 声明，我建议这样写：

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.

我认为这是可读的：从表 test_one 中删除所有“未完成”记录，表 test_two 中也存在一个条目。

回答by Praveen

You can use Mergecommand in Oracle.

您可以Merge在命令中使用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';