From: alan@effluvia.den.mmc.com (Alan Popiel - 303/977-9998)
Newsgroups: comp.databases.informix
Subject: Re:  UPDATE problem on UNIQUEly INDEXed table on DOS
Date: 7 Jan 1993 13:28:08 -0500
Organization: Mailing List Gateway
Distribution: world
Reply-To: alan@effluvia.den.mmc.com (Alan Popiel - 303/977-9998)
NNTP-Posting-Host: emory.mathcs.emory.edu
X-Informix-List-ID: <list.1758>

->From: pgf@hara.fct.unl.pt (Pedro Geraldes Freire)
->Subject: UPDATE problem on UNIQUEly INDEXed table on DOS 
->Date: Wed, 6 Jan 1993 09:36:42 GMT
->Reply-To: pgf@hara.fct.unl.pt (Pedro Geraldes Freire)
->Organization: Universidade Nova de Lisboa, PORTUGAL
->
->I'm having a problem running an UPDATE statement on a table. I
->understand the nature and reason of this problem however, I think there
->should be a way around it.
->
->I have this database table
->
->fartigos
->(
->    cod_1     char(8) not null,
->    c_int_g    smallint not null,
->    c_int_c    smallint not null,
->    c_int_art    smallint not null,
->    ...other data fields
->)
->
->with this single index
->
->unique index fartigos_ix on fartigos(cod_1, c_int_g, c_int_c, c_int_art)
->
->where the indexed fields are a composite key, and all these fields
->except for c_int_art are external keys. The c_int_art field acts as a
->local key and is also used to maintain an indexing on the records
->associated with the same (cod_1, c_int_g, c_int_c) data group, i.e. for
->each data group c_int_art must always range from 1 to N, where N is the
->number of records associated with that data group.
->
->This means that when a record must be inserted for a particular data
->group somewhere in the middle of  the sequence, say at position K such
->that  1 <= K <= N, the rows that have a c_int_art  >= K must have their
->c_int_art field incremented in order to open a "gap" for the incoming record.
->To perform such action, I use this SQL statement
->
->UPDATE fartigos
->   SET c_int_art = c_int_art + 1
->   WHERE cod_1 = ...
->         AND c_int_g = ...
->         AND c_int_c = ...
->         AND c_int_art >= K
->
->which should do the job. But it doesn't!
->When the statement is executed, it proceeds by looking up the first row
->that satisfies the condition, updating it and then repeats the process
->with the next row and so on, until there are no more rows to update. The
->question is that, when updating a row that has c_int_art = P and row P+1
->exists and hasn't been updated (yet), after the update you'll have 2
->rows P+1 and that violates the UNIQUE INDEX constraint, which causes the
->statement (and the program) to be aborted with the following error message:
->
->"Could not update a row in the table"
->SQL -346
->ISAM -100
->
->What annoys me about this is that:
->
->1. removing the UNIQUE constraint from the index is a poor (although
->simple) solution because the constraint makes sense and is rightly placed;
->
->2. because I somehow feel that a SQL statement shoud be atomic i.e. if
->the database is in a consistent state before the statement is carried
->out, and the statement is a valid and legal SQL statement which would
->render the database to a consistent state, then the way the statement is
->carried out (and the intermediate states it may generate) shouldn't
->interfere in its successful completion.
->
->Would anybody give me some hints, alternative solutions, advice, whatever?
->
->Thanks.
->
->Pedro.
->
->Pedro Geraldes Freire             | BITNET/Internet: pgf@fct.unl.pt
->Projecto CIMTOFI	       	    |            UUCP: pgf@unl.uucp
->UNINOVA - GRI - FCT/UNL           | Fax:   (+351) (1) 295 56 41/44 61
->2825 Monte Caparica, PORTUGAL     | Phone: (+351) (1) 295 44 64 ext.1560
->
->
Hello, Pedro,

This is Alan, again.

The solution proposed by Bharat Shah should work, but it involves two sets of 
updates to the database.

*IF* you are working in 4GL, then you can try something like this, which only 
does one set of updates:

DECLARE sel_cursor CURSOR FOR
SELECT ROWID, c_int_art
  FROM fartigos
 WHERE ...
   AND c_int_art >= K
 ORDER BY c_int_art DESC		{ This is the trick to make this work. }

FOREACH sel_cursor INTO my_row_id, my_int_art
  UPDATE fartigos
     SET c_int_art = my_int_art + 1	{ or whatever increment is needed }
   WHERE ROWID = my_row_id
END FOREACH

INSERT INTO fartigos
         ( ..., c_int_art )
  VALUES ( ..., K )

About what annoys you:
1. DON'T do this!!!  It will foul up your database seriously.  For example, first, 
   c_int_art = 5 -> 6; then both 6 -> 7, then all three 7 -> 8, and it gets worse!
2. I understand your stand on the theory, but practical constraints sometimes keep 
   our programs from approaching the theoretical ideal.  SQL is in theory 
   non-sequential, but our computers must operate sequentially.

By the way, what is the English translation of "fartigos"?

Regards,
Alan
+------------------------------+---------------------------------------+
| R. Alan Popiel               | Internet: alan@den.mmc.com            |
| Martin Marietta, LSC         | ( Please note: My opinions do not   ) |
| P.O. Box 179, M/S 5422       | ( represent official Martin policy. ) |
| Denver, Colorado 80201-0179  | Voice: 303-977-9998                   |
+------------------------------+---------------------------------------+