如何在 Oracle SQL 上使用内部联接删除记录？

How to delete a record using inner join on Oracle SQL?

提问by Chinovski

I have 3 tables

我有3张桌子

Table A :

表一：

| id_A | Data ... |

Table B :

表乙：

| id_B | Data ... |

Join A_B

加入 A_B

| id_A | id_B |

Those tables are an example of my situation. Well, I am trying to delete many records from Join A_B depending on many conditions like the name of A is X and name of B is Y.

这些表是我的情况的一个例子。好吧，我正在尝试根据许多条件从 Join A_B 中删除许多记录，例如 A 的名称是 X，B 的名称是 Y。

When I execute a SELECT it works, but with DELETE It doesn't.

当我执行 SELECT 时，它可以工作，但 DELETE 则不行。

This is my resquest:

这是我的要求：

DELETE A_B FROM A 
INNER JOIN A_B 
  ON (A.ID = A_B.A_ID) 
INNER JOIN B 
  ON (B.ID = A_B.B_ID) 
WHERE B.NAME IN ('X', 'Y') 
AND A.NAME = 'Z';

It says :

它说 ：

Erreur SQL : ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

回答by Stephen

Only one From table is allowed when performing a Delete. try

执行删除时只允许一个 From 表。尝试

DELETE FROM A_B 
WHERE EXISTS (SELECT 1 FROM A 
  INNER JOIN B ON (B.ID = A_B.B_ID) 
  WHERE A.ID = A_B.A_ID
  AND B.NAME IN ('X', 'Y') 
  AND A.NAME = 'Z');

reference here

参考这里

回答by JohnHC

Try:

尝试：

delete from A
where A.ID in
    (
    select A_ID
    from A_B
    inner join B
      on B.ID = A_B.B_ID
    where B.NAME in (...)
    )
and A.NAME = 'Z'

回答by Kacper

You can try merge

你可以试试合并

merge into A
using (select * from A_B inner join B on (B.ID = A_B.B_ID) where B.NAME IN ('X', 'Y')) d
on (d.A_ID = A.ID)
when matched then delete where A.NAME = 'Z';