oracle 使用WHERE条件在其他表中删除Oracle中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9735988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting a table in Oracle with WHERE condition in other tables
提问by Tuan
I have two tables, A and B, in Oracle:
我在 Oracle 中有两个表 A 和 B：
A (a1, a2)
B (b1, b2, b3, b4, b5)
And some rows like:
和一些行，如：
A(type1, 192.168.94.1)
A(type1, 192.168.94.2)
A(type2, 192.168.94.1)
B(type1, 192, 168, 94, 1)
B(type1, 192, 168, 94, 3)
B(type2, 192, 168, 94, 2)
In table A, we have A(type1, 192.168.94.1)
在表 A 中，我们有A(type1, 192.168.94.1)
In table B, we also have B(type1, 192, 168, 94, 1)
在表 B 中，我们还有B(type1, 192, 168, 94, 1)
In table A, I want delete the row
在表 A 中，我想删除该行
A(type1, 192.168.94.1)
Because its type like the type in table B
因为它的类型就像表B中的类型
A.a1 = B.b1
-> type1 = type1
and the ip address duplicate when concatenating in B
并且在B中连接时IP地址重复
A.a2 = B.b2 ||'.'|| B.b3 ||'.'|| B.b4 ||'.'|| B.5
-> 192.168.94.1 = 192.168.94.1
Could you give me some advice?
你能给我一些建议吗？
回答by turbanoff
delete from A
where ROWID in
(select A.ROWID
from A,B
where A.A1 = B.B1
and A.A2 = concat(B.B2, B.B3, B.B4)
)
回答by beny23
I suspect that you're not finding anything to delete because you may not be joining the two tables correctly, because your concat will give you concat('192', '168', '94', '1') = '192168941', which is different to '192.168.94.1'
我怀疑您没有找到要删除的任何内容，因为您可能没有正确连接这两个表，因为您的 concat 会给您concat('192', '168', '94', '1') = '192168941'，这与'192.168.94.1'
delete from A a
where exists (
select 1
from B b
where a.a1 = b.b1
and a.a2 = b.b2 || '.' || b.b3 || '.' || b.b4 || '.' || b.b5)
EDIT:
编辑：
Using concatfor concatenating nine parameters resulted in a too many parametersexception, so I'm using the ||operator instead.
采用concat了串联导致了九个参数too many parameters异常，所以我使用的||操盘手。
回答by all about data
I come from the SQL Server world, so I tried this:
我来自 SQL Server 世界，所以我尝试了这个：
delete
from E_CRM_ACTIVITYPOINTER ap
inner join tmp_activitypointer_for_delete d
on AP.ACTIVITYID = d.activityid
where ap.current_flag = 1
and d.start_date != AP.START_DATE
That didn't work in my Oracle 11g enviroment, so I modified to this:
这在我的 Oracle 11g 环境中不起作用，所以我修改为：
delete
from E_CRM_ACTIVITYPOINTER
where (ACTIVITYID,START_DATE) in
(
select AP.ACTIVITYID, AP.START_DATE
from E_CRM_ACTIVITYPOINTER ap
inner join tmp_activitypointer_for_delete d
on AP.ACTIVITYID = d.activityid
where ap.current_flag = 1
and d.start_date != AP.START_DATE
)
Hopefully this will help someone else.
希望这会帮助别人。
回答by Ollie
You haven't given us much information about what exactly you are trying to do but I assume you wish to delete rows from Awhere there is a link to B, you could try:
您还没有向我们提供关于您究竟要做什么的太多信息，但我假设您希望从A有链接的地方删除行B，您可以尝试：
DELETE FROM a
WHERE EXISTS
(SELECT 1
FROM B
WHERE a.a1 = b.b1
AND a.a2 = CONCAT(b.b2, b.b3, b.b4));
Assumptions:
假设：
1) The links between table Aand Bare correct in your original post
2) You are only wanting to delete the records from table A
1）表A和B原始帖子中的链接是正确的
2）您只想从表中删除记录A
Hope it helps...
希望能帮助到你...
EDIT:After your new comments the question is murkier still.
I do not know whether you want to delete just the IP column data or the duplicate rows from table A.
编辑：在您发表新评论后，问题仍然更加模糊。我不知道您是要删除表中的 IP 列数据还是重复的行A。
If you want to just delete the duplicate rows from table Athen it is simple:
如果您只想从表中删除重复的行，A那么很简单：
DELETE FROM A
WHERE rowid NOT IN
(SELECT MIN(rowid)
FROM A
GROUP BY a1, a2);
This assumes that columns a1 and a2 are your primary key for table A.
这假设列 a1 和 a2 是 table 的主键A。
Could you also indicate why you think you need to link to table Bat all?
您能否还指出为什么您认为完全需要链接到表格B？
