> I *mostly* agree with Aaron. A couple of points:
> 1. For some DB engines, GROUP BY doesn't work in sub-queries (cor-related
> queries). In Aaron's SQL, I'd say the GROUP BY is unnecessary anyway due to
> the rest of his WHERE clause which, in conjunction with the COUNT(*),
> narrows things down to a single record result anyway.
Cool you mean it will work... ;) I haven't checked but we do something similar I think
> 2. Aaron's SQL will delete ALL order records for a particular customer if
> that customer has more than 100 orders. I think you want to keep the first
> 100 or something? I base this assumption on your mention of "some_field",
> which I also assume to be a sequential id.
> Here's my suggestion, a modification of Aaron's SQL:
> If you want to keep the LAST 100 orders for the customer:
v.v.nice ;)
Incidentally how SQL implementations do restrict this type of SQL - is it mainly
just the grouping functions that are limited or are subqueries in general weakened
on a lot of SQL implementations.
It's useful to have the
INSERT IN DATA_AUDIT (AUDITSTAMP,A,B,C,D)
SELECT SYSDATE,A,B,C,D FROM DATA WHERE ...;
followed by the matching
DELETE FROM DATA WHERE ...;
Nice and tidy, can run on a schedule...
--
Aaron Scott-Boddendijk
Jump Productions
(07) 838-3371 Voice
(07) 838-3372 Fax
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
