Deleting unmatched records from a file in iseries SQL
I'm trying to delete unmatched records from file A based on file B. The files are matched by ListNo.
Code: DELETE FROM DVMURRAY/INSRP100 WHERE SHListNo = ALL ( SELECT SHListNo FROM DVMURRAY/INSRP100 Left Outer Join DVMURRAY/INSRP200 on SHListNo = SDListNo WHERE SDListNo is Null ) Row not found for DELETE.
The select statement work file, returning a list of unmatched records,
Not being a SQL guru, I need some help. This is for an RPGSQL program.
Thanks Phil
V5R4
Software/Hardware used:
Code: DELETE FROM DVMURRAY/INSRP100 WHERE SHListNo = ALL ( SELECT SHListNo FROM DVMURRAY/INSRP100 Left Outer Join DVMURRAY/INSRP200 on SHListNo = SDListNo WHERE SDListNo is Null ) Row not found for DELETE.
The select statement work file, returning a list of unmatched records,
Not being a SQL guru, I need some help. This is for an RPGSQL program.
Thanks Phil
V5R4
Software/Hardware used:
ASKED:
November 24, 2010 5:31 PM
UPDATED:
November 24, 2010 8:34 PM
Answer Wiki:
Original command:
<pre>DELETE FROM DVMURRAY/INSRP100
WHERE SHListNo = ALL
( SELECT SHListNo FROM DVMURRAY/INSRP100 Left Outer Join
DVMURRAY/INSRP200
on SHListNo = SDListNo
WHERE SDListNo is Null
)
Row not found for DELETE. </pre>
There could be several ways to do it.
Here are some examples, although I'm not sure the syntax for the first one is available on your platform.
Some options would berform better.
<pre>DELETE FROM DVMURRAY/INSRP100 AS i100
WHERE NOT EXISTS
(SELECT 1 FROM DVMURRAY/INSRP200
WHERE SDListNo = i100.SHListNo)</pre>
<pre>DELETE FROM DVMURRAY/INSRP100
WHERE SHListNo NOT IN
(SELECT SDListNo
FROM DVMURRAY/INSRP200)</pre>
A variation of your original command:
<pre>DELETE FROM DVMURRAY/INSRP100
WHERE SHListNo IN
(SELECT SHListNo
FROM DVMURRAY/INSRP100 Left Outer Join DVMURRAY/INSRP200
ON SHListNo = SDListNo
WHERE SDListNo is Null)</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
Thank You
Did the trick.