UPDATE problem on UNIQUEly INDEXed table on DOS

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