Обсуждение: DELETE with JOIN syntax
I am currently migrating from MySQL to PostgreSQL and I have found that some queries do not work. For instance, DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; works in MySQL. This works as expected even though the MySQL documentation does not mention the option of having a table between the keywords DELETE and FROM. I am trying to achieve the same affect for PostgreSQL so I tried DELETE FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; and it did not work. Can someone explain to me exactly what is wrong with this syntax? Is a table expression produced by the JOIN allowed for a DELETE? Im thinking that this would not work because the table expression is not a real table and it would not make sense for DELETE to accept such a parameter. How can I rewrite this query to achieve the same affect? Thanks.
On Wed, Jul 27, 2005 at 15:28:36 -0400, Brian Wong <bwlist@gmail.com> wrote: > I am currently migrating from MySQL to PostgreSQL and I have found > that some queries do not work. For instance, > > DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; > > works in MySQL. This works as expected even though the MySQL > documentation does not mention the option of having a table between > the keywords DELETE and FROM. > > I am trying to achieve the same affect for PostgreSQL so I tried > > DELETE FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; > > and it did not work. Can someone explain to me exactly what is wrong > with this syntax? > Is a table expression produced by the JOIN allowed for a DELETE? > Im thinking that this would not work because the table expression is > not a real table and it would not make sense for DELETE to accept such > a parameter. How can I rewrite this query to achieve the same affect? > Thanks. In 8.1 you will be able to use 'USING' to do this or something like it. For now, I don't think you can use explicit join syntax and need to do something like: DELETE FROM t1 WHERE t1.column_in NOT IN (SELECT column_id FROM T2); This assumes there aren't any NULL values in t2.column_id. If there are, you can rewrite the above to use NOT EXISTS.
On Wed, 27 Jul 2005, Brian Wong wrote: > I am currently migrating from MySQL to PostgreSQL and I have found > that some queries do not work. For instance, > > DELETE t1 FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; > > works in MySQL. This works as expected even though the MySQL > documentation does not mention the option of having a table between > the keywords DELETE and FROM. > > I am trying to achieve the same affect for PostgreSQL so I tried > > DELETE FROM t1 LEFT JOIN t2 USING (column_id) WHERE t2.column_id IS NULL; > > and it did not work. Can someone explain to me exactly what is wrong > with this syntax? It's mostly that AFAIK SQL has no equivalent syntax. > Is a table expression produced by the JOIN allowed for a DELETE? > Im thinking that this would not work because the table expression is > not a real table and it would not make sense for DELETE to accept such > a parameter. How can I rewrite this query to achieve the same affect? I think the where t2.column_id is null where column_id is the joining column makes this a form of not exists, so maybe: DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.column_id = t1.columnid);
On 7/27/05, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > I think the where t2.column_id is null where column_id is the joining > column makes this a form of not exists, so maybe: > > DELETE FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.column_id = > t1.columnid); > > This looks good. Thanks.