Skip site navigation (1) Skip section navigation (2)

Delete with foreign keys

From: felix(at)crowfix(dot)com
To: pgsql-sql(at)postgresql(dot)org
Subject: Delete with foreign keys
Date: 2008-02-22 18:00:21
Message-ID: 20080222180021.GA7700@crowfix.com (view raw or flat)
Thread:
Lists: pgsql-sql
I have three tables --

    CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20));
    CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id));
    CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id));

I want to delete all place and data rows which reference specific
names, but not the names themselves.  I can do it like this:

    DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'));

    DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%');

but it seems rather roundabout, and I wonder whether the EXISTS and IN
business is slow.  Is there some way to do it using JOINs?  I think of
something like this:

    DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%';

but I don't want to delete the name rows.  Then I think of this:

    DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%';

but I feel uneasy about the two separate name references when the
table is not named in the FROM clause.  Maybe that's just my novicity.

I also wonder about getting fancy and ending up with SQL specific to a
database; I don't have any plans to migrate, but I try to avoid
branding my SQL.

-- 
            ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
     Felix Finch: scarecrow repairman & rocket surgeon / felix(at)crowfix(dot)com
  GPG = E987 4493 C860 246C 3B1E  6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

Responses

pgsql-sql by date

Next:From: Ken JohansonDate: 2008-02-22 18:57:54
Subject: SQL standards in Mysql
Previous:From: johnfDate: 2008-02-22 16:30:56
Subject: Re: postgresql function not accepting null values inselect statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group