SQL Delete with subselect

[Guest.Visitor]

2ndTim wrote: > Pseudocoding the example delete from file alloc where order and > operation matches file matl and quantity issued in matl = quantity > requred in matl. Something like this should work: Delete from Alloc a Where exists (Select * from Matl m Where a.order=m.order and a.qtyiss=m.qtyiss) As a test before a delete I will usually substitute "Select * from" for "Delete from". This will show the records that are to be deleted. Bill

[2ndtim]

SQL Delete with subselect

Bill ... just what the doctor ordered. Thanx. I too do a select in interactive SQL then F9 it and change it to a delete the first time.

[dacust]

SQL Delete with subselect

As an aside, to create a file in QM like you can in interactive STRSQL, create a QM Procedure (default source file name is QQMPRCSRC). Source member type can be blank, or TXT, or whatever. 'RUN QUERY queryname'
'SAVE DATA AS library.file' The quotes are necessary. Run it with STRQMPRC. -dan

[2ndtim]

SQL Delete with subselect

Is there any way to delete a record in SQL while checking fields in other files. For example I need to delete a record in an allocation file if a certain criteria happens in some material allocation files. Pseudocoding the example delete from file alloc where order and operation matches file matl and quantity issued in matl = quantity requred in matl. I read that a suggestion is to create a file with a select statement with a join, then you could use the delete with a subselect. In interactive SQL, I can do that by changing the session attrubutes to output to a file. However if this is in a source member (RPG, QMQRY) how do I tell it to output a file? Tnx in advance.