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@crowfix.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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sat, Feb 23, 2008 at 12:42:13PM +0530, dipesh wrote:

> alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id)
> REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE;

Interestingly different way of doing it. Thanks. It makes me think
too :-)

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly