Re: UPDATE problem on UNIQUEly INDEXed table on DOS

->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 | +------------------------------+---------------------------------------+