sql - sql语句语法错误

Question

我有一个看起来像这样的 sql 语句:

delete from survey_results sr
left outer join people p on p.id = sr.person_id
left outer join survey_result_sets srs on srs.id = sr.survey_result_set_id
left outer join survey_schemas ss on ss.id = sr.survey_schema_id
where (sr.person_id is not null and p.id is null)
OR (srs.id is null)
OR (ss.id is null);

但它给出了一个语法错误:

ERROR:  syntax error at or near "left"
LINE 2: left outer join people p on p.id = sr.person_id

这是为什么？

Answer 1

在 PostgreSQL 中，您不能在 DELETE 语句中使用 JOIN。而是使用 USING 并将第二个表放在那里。像这样的东西应该可以工作

   delete from survey_results sr
using people p on p.id = sr.person_id
using  survey_result_sets srs on srs.id = sr.survey_result_set_id
using  survey_schemas ss on ss.id = sr.survey_schema_id
where (sr.person_id is not null and p.id is null)
OR (srs.id is null)
OR (ss.id is null);

可以引用这个PostgreSQL-delete