CODEWITHSUNDEEP
sqlpostgresql
ntonnelier
ntonnelier

Reputation: 1549

syntax error on DELETE FROM LEFT INNER JOIN

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?

Upvotes: 0

Views: 216

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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

Upvotes: 2

mbrother
mbrother

Reputation: 26

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."?

Upvotes: 0

Aurelien
Aurelien

Reputation: 458

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

Upvotes: 0

Related Questions