Deleting unmatched records from a file in iseries SQL

530 pts.
Tags:
iSeries SQL
iseries v5r4
RPGSQL
SQL
V5R4
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

ASKED: November 24, 2010  5:31 PM
UPDATED: November 24, 2010  8:34 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • PSMurray
    Thank You
    WHERE SHListNo IN 
    Did the trick.
    530 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following