CODEWITHSUNDEEP
sqloracle-databaseinner-join
Chinovski
Chinovski

Reputation: 517

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

I have 3 tables

Table A :

| id_A | Data ... |

Table B :

| id_B | Data ... |

Join 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.

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

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"

Upvotes: 4

Views: 30559

Answers (3)

Stephen
Stephen

Reputation: 1542

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

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

Upvotes: 12

Kacper
Kacper

Reputation: 4818

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';

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

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'

Upvotes: 3

Related Questions