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;
HTH,
Carl Reynolds Ph: +64-9-4154790
CJN Technologies Ltd. Fax: +64-9-4154791
[EMAIL PROTECTED] DDI: +64-9-4154795
PO Box 302-278, North Harbour, Auckland, New Zealand
12 Piermark Drive, North Harbour Estate, Auckland, NZ
Visit our website at http://www.cjntech.co.nz/
> -----Original Message-----
> From: Grant Black [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 12, 1999 2:32 PM
> To: Multiple recipients of list database
> Subject: [DUG-DB]: Common SQL Query
>
> Its a pretty common sort of query but I am not sure which way to do it
> �
> I want to�count the number of�files belonging to a CAD,�(think of it
> as
> counting the number of widgets a customer has brought), so that I can
> prune the database if a CAD has more than 100 files.
> �
> 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...
> �
> My�brain is going to mush - I used to be able to do these queries no
> problem..
> �
>
> Grant Black
> Software Developer
> SmartMove (NZ) Ltd
> Phone:���� +64 9 361-0219 extn 719
> Fax� :���� +64 9 361-0211
> Email:���� [EMAIL PROTECTED]
>
> ----------------------------------------------------------------------
> -----
> New Zealand Delphi Users group - Database List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
application/ms-tnef
