Thread: Joined delete

Hi all,

   After all the great help I got from you all I managed to finish what
I was struggling with. The last thing I need to do should be pretty
simple. :)

   I use this query to return values from one table that don't exits in
another table:

SELECT a.fs_name, a.fs_parent_dir, a.fs_type FROM file_set_1 a LEFT JOIN
file_info_1 b ON a.fs_name=b.file_name AND
a.fs_parent_dir=b.file_parent_dir AND a.fs_type=b.file_type WHERE
b.file_name IS NULL;

   Which works perfectly. What I need to do though is instead of
returning those value, I need to instead delete the matches. I tried:

DELETE FROM file_set_1 a LEFT JOIN file_info_1 b ON
a.fs_name=b.file_name AND a.fs_parent_dir=b.file_parent_dir AND
a.fs_type=b.file_type WHERE b.file_name IS NULL;

   But I am getting the syntax error:

ERROR:  syntax error at or near "a" at character 24

   This is probably obious... ^.^;

Thank you again!

Madison

You can't use LEFT JOINs or really any kind of explicit join in a
DELETE query (deficiency of the SQL standard). If you need a LEFT JOIN
you probably need a subselect, like:

DELETE FROM table WHERE primary_key IN
  (SELECT primary_key FROM x LEFT JOIN y WHERE etc..)

or any of the equivalent forms...

Hope this helps,

On Mon, Jan 03, 2005 at 12:39:38AM -0500, Madison Kelly wrote:
> Hi all,
>
>   After all the great help I got from you all I managed to finish what
> I was struggling with. The last thing I need to do should be pretty
> simple. :)

<snip>

>   Which works perfectly. What I need to do though is instead of
> returning those value, I need to instead delete the matches. I tried:
>
> DELETE FROM file_set_1 a LEFT JOIN file_info_1 b ON
> a.fs_name=b.file_name AND a.fs_parent_dir=b.file_parent_dir AND
> a.fs_type=b.file_type WHERE b.file_name IS NULL;
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.