DELETE with left join on Oracle Database

Question

I have following sql statement that works on MSSQL:

DELETE VERTICALCALCULATIONRULE FROM VERTICALCALCULATIONRULE
        LEFT JOIN ATTRIBUTE ON ATTRIBUTE.ID = VERTICALCALCULATIONRULE.ATTRIBUTEID 
        WHERE NOT EXISTS (SELECT * FROM ORGANISATIONUNIT  WHERE ORGANISATIONUNIT.ID = ATTRIBUTE.ORGANISATIONUNITID);

and i use this for postgres databases:

delete from verticalcalculationrule
        using attribute
        where attribute.id = verticalcalculationrule.attributeid and not exists (select * from organisationunit where organisationunit.id = attribute.organisationunitid)

How can i do this deletion on a Oracle Database. When I use this sql statements DBeaver shows me this Error:

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)

at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)

at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1737)

at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)

at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:300)

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:327)

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)

... 12 more

Caused by: Error : 933, Position : 31, Sql = DELETE VERTICALCALCULATIONRULE FROM VERTICALCALCULATIONRULE

        LEFT JOIN ATTRIBUTE ON ATTRIBUTE.ID = VERTICALCALCULATIONRULE.ATTRIBUTEID 

        WHERE NOT EXISTS (SELECT * FROM ORGANISATIONUNIT  WHERE ORGANISATIONUNIT.ID = ATTRIBUTE.ORGANISATIONUNITID), OriginalSql = DELETE VERTICALCALCULATIONRULE FROM VERTICALCALCULATIONRULE

        LEFT JOIN ATTRIBUTE ON ATTRIBUTE.ID = VERTICALCALCULATIONRULE.ATTRIBUTEID 

        WHERE NOT EXISTS (SELECT * FROM ORGANISATIONUNIT  WHERE ORGANISATIONUNIT.ID = ATTRIBUTE.ORGANISATIONUNITID), Error Msg = ORA-00933: SQL-Befe

Answer 1

If I following the logic correctly, this should be equivalent:

DELETE VERTICALCALCULATIONRULE vcr 
    WHERE NOT EXISTS (SELECT 1
                      FROM ATTRIBUTE a JOIN
                           ORGANISATIONUNIT ou
                           ON ou.ID = a.ORGANISATIONUNITID
                      WHERE a.ID = vcr.ATTRIBUTEID 
                     );

This deletes rules where there is no organization associated with any attributes of the rule. I would recommend that you test this before permanently changing the table.