Skip site navigation (1) Skip section navigation (2)

Re: help deleting obsolete records

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: help deleting obsolete records
Date: 2002-10-16 19:25:20
Message-ID: 5.1.1.6.0.20021016152156.02f3b930@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
At 03:28 PM 10/15/02, Frank Bax wrote:
>I don't know how to write an SQL for the following:
>
>Table "teamwork"
>  Attribute |           Type       |  Modifier
>-----------+----------------------+-------------
>  team      | character varying(8) | not null
>  emp       | character varying(2) | not null
>
>Table "timesheet"
>  Attribute |           Type           |  Modifier
>-----------+--------------------------+-------------
>  emp       | character varying(8)     | not null
>  team      | character varying(2)     | not null
>  lo_shift  | timestamp with time zone |
>  hi_shift  | timestamp with time zone |
>
>Consider a subset of data from timesheet which meets the condition:
>         lo_shift > (now()-'30days'::interval)
>Now I want to delete all rows from teamwork where the data pair "emp,team" 
>is *not* in the subset of data retrieved from timesheet.  It is possible 
>that timesheet will contain multiple rows for any given emp,team pair.


Amazing what 24hrs can do..  Is combining emp & team the only way to do 
this, as in:

DELETE  FROM  teamwork  WHERE  emp||':'||team  NOT IN  ( 
SELECT  DISTINCT  emp||':'||team  FROM  timesheet    WHERE   lo_shift > 
(now()-'30days'::interval) );

Frank


In response to

pgsql-novice by date

Next:From: Devinder K RajputDate: 2002-10-16 19:46:57
Subject: Re: newbie qs; examining databases and tables
Previous:From: Josh BerkusDate: 2002-10-16 16:19:41
Subject: Re: db design question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group