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
Unconfigured Ad Widget
Collapse
Announcement
Collapse
No announcement yet.
SQL Delete with subselect
Collapse
X
-
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
Comment
-
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.
Comment
Comment