>> The psuedo would do something like:
>> for each Customer in the Customer table do
>> select count(widgets)
>> from orders
>> if count_widgets > 100 then
>> Delete_widgets from orders where (some_field > 100_value)
>> I guess I can do the first part with a parameterised query...
>Query1:
>select * from customers
>Query2:
>select orders.order_no, count(*) widget_count
>from widgets, orders
>where orders.order_no = widgets.order_no
>and orders.customer_no = :customer_no
>group by order_no
>having widget_count > 100
>Query2.DataSource := DataSource1; // DataSource1 has Query1 as its
>DataSet
>Query1.Open;
>while not Query1.EOF do begin
>// Can't remember whether you need to open and close Query2
>explicitly
>while not Query2.EOF do begin
>// Delete 100 - x widgets from this order - for example
>DeleteSomeWidgets( Query2order_no.AsInteger,
> 100 - Query2widget_count.AsInteger );
>Query2.Next;
>end;
>Query1.Next;
>end;
DELETE FROM
ORDERS A
WHERE
100<(
SELECT
COUNT(*)
FROM
ORDERS O
WHERE
O.CUST=A.CUST
GROUP BY O.CUST
)
works doesn't it...
IE Find the counts of records in the orders table for each customer
and delete those orders for customers with more than 100 records.
--
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
