Search code examples
sqloraclejoinsql-delete

How to delete a row from a relationship table using two columns

I'm trying to delete a relationship between table T2 and T3 that is on table T1. I've searched but I could only find examples of a DELETE with only one INNER JOIN.

I'm trying something like this, but I get a error:

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

DELETE t1
FROM T1 t1
INNER JOIN T2 t2
  ON t2.ID = t1.ID_t2
INNER JOIN T3 t3  
  ON t3.ID = t1.ID_t3
  WHERE (t3.property1 IN 'XXX' AND 
        t3.property2 = 1 AND 
        t2.property = 'YYY');

How can I get this to work?

Solution

  • You are using MySql syntax to delete from an Oracle database.
    You can do it with EXISTS:

    DELETE FROM T1 
WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.property = 'YYY' AND T2.ID = T1.ID_t2)
  AND EXISTS (SELECT 1 FROM T3 WHERE T3.property1 = 'XXX' AND T3.property2 = 1 AND T3.ID = T1.ID_t3)