CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Syntax error in sql statement

I have an sql statement that looks like this:

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

but it gaves an syntax error:

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

Why is that?

Upvotes: 0

Views: 186

Answers (2)

Soni Harriz
Soni Harriz

Reputation: 3568

How about :

begin;
create temp table table_survey_result_pk on commit drop as select survey_result_pk 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);
delete from survey_results t using table_survey_result_pk d on t.survey_result_pk = d.survey_result_pk;
commit;

Upvotes: 1

backtrack
backtrack

Reputation: 8144

You can't use JOIN in DELETE statement in PostgreSQL. Instead use USING and put the second table there. Something like this should work

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

You can refer this PostgreSQL-delete

Upvotes: 2

Related Questions