Re: SQL - delete with ... select by two fields?

Tomek,

Sometimes what I do is to create a view that "wraps" the
SELECT statement I want and, if I need to delete all the
range, a DELETE instruction (pointing to the view) without
WHERE is enough (something that is always done with a
little panic).

Regards,

Luis Rodriguez
--- midrange-l-request@xxxxxxxxxxxx wrote:

------------------------------

message: 8
date: Wed, 14 Nov 2007 18:00:40 +0100
from: Tomasz Skor?a <t.skorza@xxxxxxxxxxx>
subject: SQL - delete with ... select by two fields?

Hi guys

I have File with many fields i.e. doc_nr, batch_nr,
amount and other. (pair
doc_nr and batch_nr are unique)
Then I create select statement:

select doc_nr, batch_nr from File
group by doc_nr, batch_nr
having sum(amount)=0

which take all records with sum of amount = 0 for pair:
doc_nr and batch_nr.

But how I should create DELETE statement which do it for
the same
parameters.

example

File

doc_nr batch_nr amount
1 1 100
1 1 -100
1 2 100
1 2 -120
3 4 100


select take records
doc_nr batch_nr amount
1 1 100
1 1 -100

and this records should be deleted so file after delete
should contain only
doc_nr batch_nr amount
1 2 100
1 2 -120
3 4 100

Have you any idea?

Regards

Tomek

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


____________________________________________________________________________________
Be a better sports nut! Let your teams follow you
with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ