Delete using left outer join in Postgres
postgresql
Solution
As others have noted, you can't LEFT JOIN directly in a DELETE statement. You can, however, self join on a primary key to the target table with a USING statement, then left join against that self-joined table.
Note the self join on tv_episodes.id in the WHERE clause. This avoids the sub-query route provided above.
DELETE FROM tv_episodes
USING tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND
ed.path = nd.path
WHERE
tv_episodes.id = ed.id AND
ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;
Problem
I am switching a database from MySQL to Postgres SQL. A select query that worked in MySQL works in Postgres but a similar delete query does not. I have two tables of data which list where certain back-up files are located. Existing data (ed) and new data (nd). This syntax will pick out existing data which might state where a file is located in the existing data table, matching it against equal filename and path, but no information as to where it is located in the new data: ``` SELECT ed.id, ed.file_name, ed.cd_name, ed.path, nd.cd_name FROM tv_episodes AS ed LEFT OUTER JOIN data AS nd ON ed.file_name = nd.file_name AND ed.path = nd.path WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL; ``` I wish to run a delete query using this syntax: ``` DELETE ed FROM tv_episodes AS ed LEFT OUTER JOIN data AS nd ON ed.file_name = nd.file_name AND ed.path = nd.path WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL; ``` I have tried `DELETE ed` and `DELETE ed.*` both of which render `syntax error at or near "ed"`. Similar errors if I try without the alias of `ed`. If I attempt ``` DELETE FROM tv_episodes AS ed LEFT JOIN data AS nd..... ``` Postgres sends back `syntax error at or near "LEFT"`. I'm stumped and can't find much on delete queries using joins specific to psql.