Re: SQL Delete -- MIDRANGE-L

× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.

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 mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.