Need help with delete query...

[Joe]

Hello All,

I need help creating an efficient delete query.

I have two tables with a one to many relationship. My main table has a
key ID field and several descriptive text fields for each record/row. The
second table links to the first via the keyfield, and can have one or many
more records related to each Main table record/row. One of the fields in the
second table is a date field.

I want to delete all records in the Second table whose date is older or
"<" a specified date unless it is the only record for its related record in
the Main table.

I hope I stated that clearly enough.

Any help on how to create this query with SQL would be greatly appreciated.

 

[Guest]

An interesting problem probably using sub-queries. Please provide the table
names and those of the primary and foreign keys plus the date field.

 

[Joe]

Jerry, Here are the tables . I was expecting it to require some sort of
query, subquery combo but I haven't been able to put it all together.
Please help.

Main table fields
===========
MainID - key field Long Int
Name - text
Address - text

Second Table fields
==============
ID - Key
MainID - frgn key
Date -date
Description -text

Thanks,

 

[Guest]

Try this on a backup of the database first.

DELETE Second.*
FROM [Second]
WHERE Second.MainID Not In
(SELECT Second.MainID
FROM [Second]
GROUP BY Second.MainID
HAVING Count(Second.MainID)<2)
AND Second.date<[Enter Date];

One problem is that it could still delete all the records for a MainID if
those records were older than the Date field. It only excludes those records
that currently have the only MainID.

 

[John Spencer]

RULE 1: Try this on a COPY of your data. Just in case it doesn't perform as expected.

DELETE Table2.*
FROM Table2
WHERE Table2.[Date] <
(SELECT Max(T2.[Date])
FROM Table2 as T2
WHERE T2.MainID = Table2.MainID)
AND
Table2.[Date] <= #1/1/05#

Problem with this is that if there are two or more records on the Maximum date
for any one mainID, then you will end up with all those records.

RULE 2: Try this on a COPY of your data. Just in case it doesn't perform as expected.