From: | george young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: delete from joined tables |
Date: | 2006-03-29 16:30:07 |
Message-ID: | 20060329113007.18f882ec.gry@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(dot)hedley(at)ed(dot)ac(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | operationsengineer1 | 2006-03-29 16:42:41 | Re: PHP libpq Version != Actual PGSQL Version |
Previous Message | Daniel T. Staal | 2006-03-29 14:17:03 | Re: Non-Unique intems |