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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Devinder K Rajput | 2002-10-16 19:46:57 | Re: newbie qs; examining databases and tables |
Previous Message | Josh Berkus | 2002-10-16 16:19:41 | Re: db design question |