Syntax error in sql statement

Question

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?

backtrack · Accepted Answer · 2015-05-05 05:35:28Z

2

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

edited May 5, 2015 at 5:35

answered May 5, 2015 at 5:29

backtrack

8,1545 gold badges56 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mateusz Urbański Over a year ago

When I paste your code to my database console it returns me: ERROR: syntax error at or near "sr" LINE 1: delete sr

backtrack Over a year ago

Hi @MateuszUrbański If you are using PostgreSQL then you cant use joins in the delete

Mateusz Urbański Over a year ago

Your code still gaves syntax error: ERROR: syntax error at or near "on" LINE 2: using people p on p.id = sr.person_id But when googling there are a lot of topics people using delete with joins in postgress.

Mateusz Urbański Over a year ago

You are right with this joins but I still can't make this query to work. Why this syntax error is causing?

user330315 Over a year ago

USING can only be used once in a DELETE statement it's pretty much the same as the FROM clause in a SELECT statement

Soni Harriz · Accepted Answer · 2015-05-05 10:45:22Z

1

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;

answered May 5, 2015 at 10:45

Soni Harriz

3,6481 gold badge15 silver badges8 bronze badges

Collectives™ on Stack Overflow

Syntax error in sql statement

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related