Hello Ann, Milan, and Adam!

Thank you all for your ideas!

--- In firebird-support@yahoogroups.com, Milan Babuskov <albis@e...>
wrote:

>
> Generally, yes. But in this case it has to be done for *each*

record.

> AFAIU, that means that it has to locate each record. A

simple "natural"

> update also touches all records, but it doesn't even bother with

index

> so it should be quicker. Right?

Milan, do I understand right that by "natural" update, you mean an
update on all records of a table without restriction?

In fact, the example in my initial post (UPDATE MY_TABLE SET
IX=IX+1) is a somewhat simplified presentation of our actual
setting. In reality, the table holds arrays of records for a set of
objects. Each object may contain any number of array items; the
order of the array items of an object is relevant. The primary key
is formed by the object key and the array index. We must be able to
shift index ranges for deleting/inserting array items.

For example:

UPDATE MY_TABLE SET IX = IX + 1 WHERE OBJECT_KEY = 123456789 and IX

>= 27

INSERT INTO MY_TABLE (OBJECT_KEY,IX,...other columns...) VALUES
(123456789,27,...values for other columns...)

(btw, is this such an uncommon problem setting?)

Would you under these circumstances still prefer the double-update
to the SP approach? (In our special case, there's another problem
with stored procedures: our solution should work with
Firebird/Interbase as well as with MySQL (via dbExpress). But we
might consider a two-way solution, if there's a good reason for
doing so.)

Greetings
Helmut