Re: Newbie SQL question

gressett_at_computek.net wrote:
>
> I have a table of transactions which contains records
> some of which need to be removed;
> Here is a simplified version of the table
> which we can call T:
>
> client_id varchar2(11)
> type varchar2(4)
> billed number(7, 2)
> paid number(7, 2)
>
> I have created a view V which serves
> as an identical copy of T.
>
> There are a number of records in T
> which contain in the type field the value
> 'VOID'. For every such record there is
> at least one other record with the same
> client_id and billed and paid values which
> are the negative of the corresponding values
> in the VOID record. I need to delete both the
> VOID record and at most ONE of the matching
> records. I.e, consider these two records:
>
> '12345678901' 'XXXX' 100.00 50.00
> '12345678901' 'VOID' -100.00 -50.00
>
> Both of these should be deleted.
>
> The following SQL select statement will report
> the records that need to be deleted:
>
> select
> T.client_id,
> T.type,
> T.billed,
> T.paid
> from
> T,
> V
> where (
> T.client_id = V.client_id
> and
> T.billed = - V.billed
> and
> T.paid = - V.paid
> )
>
> What SQL will delete the same records
> that this select statement finds?

Try this :
Let your SQL statement mentioned above = 'S'

delete from T where exists (S);

amit Received on Thu May 23 1996 - 00:00:00 CEST