[EMAIL PROTECTED] wrote:
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.

Hello, I am Dipesh Mistry from Ahmedabad-India.
I have the solution for you.
First drop the constraint on table data.
Example.
   alter table data drop constraint data_place_id_fkey;
And in second step add new Constraint.
Example.
alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id) REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE;

Now when you test query like,

delete from place where name_id in(select id from name where str='dip%');

So, you can try this above step.

--
With Warm Regards,
Dipesh Mistry
Information Technology Dept.
GaneshaSpeaks.com


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

Reply via email to