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); ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
