syntax error on DELETE FROM LEFT INNER JOIN

Question

Im getting the following Error on my query:

ERROR:  syntax error at or near "p"

This is my query:

  DELETE p FROM property_import_image_results p
  LEFT JOIN LATERAL (SELECT q.created_at FROM property_import_image_results q
  WHERE q.external_url=p.external_url AND (q.listing_image_id = p.listing_image_id OR q.listing_image_id IS NULL)
  ORDER BY q.created_at DESC NULLS LAST LIMIT 1) as qf ON p.created_at = qf.created_at
  WHERE qf.created_at is NULL;

Any idea what might be the problem?

You can't use left join like that in a DELETE statement: postgresql.org/docs/current/static/sql-delete.html — user330315
– user330315, Commented Dec 1, 2015 at 15:42

Gordon Linoff · Accepted Answer · 2015-12-01 22:57:10Z

2

You cannot phrase your query this way. Perhaps this is what you want:

DELETE FROM property_import_image_results p
    WHERE NOT EXISTS (SELECT 1
                      FROM property_import_image_results q
                      WHERE q.external_url = p.external_url AND
                            q.created_at > p.created_at AND 
                            (q.listing_image_id = p.listing_image_id OR  q.listing_image_id IS NULL)
                     ) ;

edited Dec 1, 2015 at 22:57

answered Dec 1, 2015 at 15:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

ntonnelier Over a year ago

thanks @Gordon Linoff but I need the ORDER BY in order to get the last created result (im basically trying to get avary result, group by external_url, except the most recent one)

Gordon Linoff Over a year ago

@ntonnelier . . . I see. I changed the = to a >, which should get the most recent version for updating.

Aurelien · Accepted Answer · 2015-12-01 15:40:59Z

0

There is no need of p between DELETE and FROM clause : http://www.w3schools.com/sql/sql_delete.asp. So try to delete it.

edited Dec 1, 2015 at 15:40

answered Dec 1, 2015 at 15:38

Aurelien

4581 gold badge5 silver badges13 bronze badges

3 Comments

Strawberry Over a year ago

meta.stackexchange.com/questions/87678/… - probably professional rivalry, but just sayin'

ntonnelier Over a year ago

Not the case when you intend to delete from JOIN. This was my first attempt actually..

Aurelien Over a year ago

@Strawberry thanks for the reference, I will use this reference to w3schools more carefully in the future.

mbrother · Accepted Answer · 2015-12-01 15:41:09Z

0

While I'm not familiar with postgresql's specifics, aren't you essentially saying "delete mytable from mytable" in the beginning of your delete statement? Shouldn't that be "delete from property_import_image_results p ...etc. etc."?

answered Dec 1, 2015 at 15:41

mbrother

263 bronze badges

1 Comment

Don't Panic Over a year ago

Your answers will generally be better received if they are not phrased as questions.

Collectives™ on Stack Overflow

syntax error on DELETE FROM LEFT INNER JOIN

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related