DELETE with left join on Oracle Database

Question

我有以下sql语句在MSSQL上工作:

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

和我使用这个postgres数据库:

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

如何在Oracle数据库上执行此删除操作?当我使用这个sql语句时，DBeaver告诉我这个错误:

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

由于:Error: 933, Position: 31, Sql = DELETE 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

如果我遵循正确的逻辑，这应该是等价的:

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

这将删除没有组织与规则的任何属性相关联的规则。我建议您在永久更改表之前对此进行测试。