Re: SQL --- DELETE FROM statement problem.

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.