Menu
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.
843
Delete left join table is used to delete rows from the left table that do not have matching records in the right table. Below is the syntax to of deleting rows with a left join that does not have matching rows in another table: Delete table1 from table1 LEFT JOIN table2 ON table1. col_name=table2.
We can also use the LEFT JOIN clause in the DELETE statement to delete rows in a table (left table) that does not have matching rows in another table (right table). Note that we only put T1 table after the DELETE keyword, not both T1 and T2 tables like we did with the INNER JOIN clause.
PostgreSQL doesn't support the DELETE JOIN statement. However, it does support the USING clause in the DELETE statement that provides similar functionality as the DELETE JOIN . In this syntax: First, specify the table expression after the USING keyword.
A DELETE statement can include JOIN operations. It can contain zero, one, or multiple JOIN operations. The DELETE removes records that satisfy the JOIN conditions.
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.
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; Note the self join on tv_episodes.id in the WHERE clause. This avoids the sub-query route provided above.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
