Thread: delete from joined tables

Hi

I want to delete rows out of table1 that match a selection based on
table2, tables joined on uniqueID.  i.e.

table1
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 est_id   | character varying(15) | not null
 sequence | text                  |

table2
              Table "public.est"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 est_id   | character varying(15) | not null
 clus_id  | character varying(10) | not null

select * from table1 natural join table2 where clus_id like 'NVC%';

Selects the ones I want to delete but

delete from table1 natural join table2 where clus_id like 'NVC%';

gives me a parse error at natural and all other delete commands I've
tried fail.

I can add the clus_id column to table1 and then delete on that column
but surely I should be able to do it in one step?

Thanks

Ann

hi

delete from table1
where est_id in (select est_id
                 from table2
                 where clus_id like 'NVC%');

yours,
christoph

ann hedley schrieb:
> Hi
>
> I want to delete rows out of table1 that match a selection based on
> table2, tables joined on uniqueID.  i.e.
>
> table1
>  Column  |         Type          | Modifiers
> ----------+-----------------------+-----------
> est_id   | character varying(15) | not null
> sequence | text                  |
>
> table2
>              Table "public.est"
>  Column  |         Type          | Modifiers
> ----------+-----------------------+-----------
> est_id   | character varying(15) | not null
> clus_id  | character varying(10) | not null
>
> select * from table1 natural join table2 where clus_id like 'NVC%';
>
> Selects the ones I want to delete but
>
> delete from table1 natural join table2 where clus_id like 'NVC%';
>
> gives me a parse error at natural and all other delete commands I've
> tried fail.
>
> I can add the clus_id column to table1 and then delete on that column
> but surely I should be able to do it in one step?
>
> Thanks
>
> Ann
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

How about:
   delete from table1 using table2 t2 where t2.est_id=table1.est_id and t2.clus_id like 'NVC%';

This is likely to be faster than the subselect mentioned by Christof,
and it's a bit simpler.

-- George Young

On Tue, 28 Mar 2006 12:04:48 +0100
ann hedley <ann.hedley@ed.ac.uk> threw this fish to the penguins:

> Hi
>
> I want to delete rows out of table1 that match a selection based on
> table2, tables joined on uniqueID.  i.e.
>
> table1
>   Column  |         Type          | Modifiers
> ----------+-----------------------+-----------
>  est_id   | character varying(15) | not null
>  sequence | text                  |
>
> table2
>               Table "public.est"
>   Column  |         Type          | Modifiers
> ----------+-----------------------+-----------
>  est_id   | character varying(15) | not null
>  clus_id  | character varying(10) | not null
>
> select * from table1 natural join table2 where clus_id like 'NVC%';
>
> Selects the ones I want to delete but
>
> delete from table1 natural join table2 where clus_id like 'NVC%';
>
> gives me a parse error at natural and all other delete commands I've
> tried fail.
>
> I can add the clus_id column to table1 and then delete on that column
> but surely I should be able to do it in one step?
>
> Thanks
>
> Ann
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)