Re: SQL --- DELETE FROM statement problem.

["Ted Barry" <TBARRY@xxxxxxxxxxxxxxxxxxxx>]

I'm out of the office today, but I'll return on Monday, May 6th. If you need 
further assistance, please contact Mark Greenawalt at x311, John Censoplano at 
x353, or Spencer Chun at x354.

Thanks,
 Ted

>>> "rpg400-l@midrange.com" 05/03/02 06:27 >>>

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Good perseverance!  I kept hammering at that first file also.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Rusling, John B. (Alliance)" <jbrusling@alliancedev.com>
Sent by: rpg400-l-admin@midrange.com
05/02/2002 01:32 PM
Please respond to rpg400-l


        To:     "'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
        cc:
        Fax to:
        Subject:        Re: SQL --- DELETE FROM statement problem.


Re: SQL --- DELETE FROM statement problem SOLVED.

***  This now works  ****

In step 2 where I create a table to hold the 'purgeable' Order Numbers, I
added the line PRIMARY KEY(killon) to get a unique key on the file and
wo-hoo, now step 7 (DELETE FROM) runs like the wind (well at least a
breeze,
it's sql).


Step 2--
CREATE TABLE purgso/spurgon(
  ( killon     CHAR (  7  )  NOT NULL WITH DEFAULT,
    PRIMARY KEY(killon) )


Step 7--
DELETE FROM so
  WHERE soon IN
    (SELECT killon
       FROM purgso/spurgon)


I'm guessing now but in retrospect the message I was getting;

  ''Message.: View or logical file SO in JBRUSLING read-only.''

was only giving me the (2nd) symptom which was caused (?) by the first
symptom of my subselect file not having a unique key (?).

Paraphrase; 'Buddy, the file whose records you want to DELETE is
read-only!'

And it should have added 'It's read-only because you don't have a unique
key
for the file in your subselect.' (?)

This is my explanation only, I haven't 'read' any manuals!


Thanks to Rob for his tips and this fella over on google ->
comp.sys.ibm.as400.misc Trond Botnen for
Subject: Re: SQL DELETE and UPDATE statements failing on AS400 using SQL
Server
....>>>>>        post on 2001-05-21.
...this problem occurs when no unique key are identified in the file.
I don't know SQL Server 7, but MS Access usually tells me to select which
fields that make up a unique key...




which gave me yet another idea.

John B.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.