Re: SQL --- DELETE FROM statement problem.

["Rusling, John B. (Alliance)" <jbrusling@xxxxxxxxxxxxxxx>]

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.