Thread: howto delete using a join ?
Hi, is there a way to delete from a table using information from another table to decide if a row should be dropped? In my case there is a log events ( event_id, event_type_fk, ...); event_types ( event_type_id, relevance_level ); Now I'd like to delete all old events with certain relevance_levels but not all! This works: delete from events where event_id in ( select event_id from events join event_types on event_type_id = event_type_fk where relevance_level in ( 1, 3, 5, 7) and create_ts < '2010/01/01' ); The following doesn't work but is there a more direct way which doesn't involve a subselect? delete from events join event_types on event_type_id = event_type_fk where relevance_level in ( 1, 3, 5, 7);
Hey Andreas,<br /><br />You may want to use the USING clause:<br /><br />DELETE FROM events USING event_types<br /> WHEREevents.event_type_fk = <a href="http://event_types.event_type.id/" target="_blank">event_types.event_type.id</a><br/> AND relevance_level IN ( 1, 3, 5, 7)<br /> AND <ANY_ANOTHER_CONDITION_YOU_<divclass="ii gt" id=":1s8">NEED> ;<br /><br />Regards,<br />Dmitriy<br /></div><br />
On Sat, Jul 24, 2010 at 06:05:57AM +0200, Andreas wrote: > Hi, > > is there a way to delete from a table using information from another > table to decide if a row should be dropped? Yes. See DELETE ... USING http://www.postgresql.org/docs/8.4/interactive/sql-delete.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com