RE: SQL Delete

["Wills, Mike N. (TC)" <MNWills@xxxxxxxxxxxxxx>]

Thanks, this will work for me. I knew it could be done, just wasn't sure
how.

-----Original Message-----
From: Vern Hamberg [mailto:vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx]
Sent: Friday, April 18, 2003 5:29 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Delete


Mike

You can't delete through a join, either, AFAIK.

You can use the WHERE field NOT IN select-statement

DELETE FROM LAWDBF7TST/DBAPVEN
    WHERE DBVEN_VENDOR NOT IN
       SELECT DBAPI_VENDOR FROM LAWDBF7TST/DBAPAPI
          WHERE DBAPI_VENDOR = DBVEN_VENDOR
    AND DBVEN_VENDOR NOT IN
       SELECT DBVBA_VENDOR FROM LAWDBF7TST/DBAPVBA
          WHERE DBVBA_VENDOR = DBVEN_VENDOR

This assumes you want to delete the record only if its matching key is not 
in either of the other files.

HTW - hope this works   ;-)

Vern

At 03:54 PM 4/18/2003 -0500, you wrote:
>Hello all,
>
>I want to do an SQL delete, but I get an error.
>My select SQL stmt is:
>SELECT *
>    FROM LAWDBF7TST/DBAPVEN
>         EXCEPTION JOIN LAWDBF7TST/DBAPAPI
>            ON DBVEN_VENDOR = DBAPI_VENDOR
>         EXCEPTION JOIN LAWDBF7TST/DBAPVBA
>            ON DBVEN_VENDOR = DBVBA_VENDOR
>                 WHERE DBVEN_VENDOR < '   100000'
>
>My delete SQL stmt is:
>DELETE FROM LAWDBF7TST/DBAPVEN
>         EXCEPTION JOIN LAWDBF7TST/DBAPAPI
>            ON DBVEN_VENDOR = DBAPI_VENDOR
>         EXCEPTION JOIN LAWDBF7TST/DBAPVBA
>            ON DBVEN_VENDOR = DBVBA_VENDOR
>                 WHERE DBVEN_VENDOR < '   100000'
>

-snip-

>As always, any ideas would be appreciated,
>
>Mike Wills
>iSeries Programmer


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.