I tried to launch a proc like below by a C++ program.
first i deactivate the index on field b , which is not primary key.
a is primary key.

But after this proc is finished without any error message.
Supposed should have 1000 records updated, finally only 900 records updated!

I check back with the same select, indeed 100 records stayed like original data, without update.

Anybody can help me to check out the problem? the foreach doesn't scan the whole table thoroughly with the where condition?

After this update, we enable the index on field b again.

proc :

drop procedure test();

create procedure test()
returning integer;

{Variables pour la gestion des erreurs INFORMIX}
define esql, eisam integer;
define error_text char(255);

define compteur integer; -- nombre de ligne traitees

define key_name1 like table.a;
define field like table.b;

let compteur = 0;
set debug file to 'test.out' ; trace on;

begin
on exception set esql, eisam, error_text
raise exception esql, eisam, error_text;
return;
end exception

foreach with hold
select
a,
b
into
key_name1,
field
from table
where (length(b)=11) and (b >= '96560000000') and (b <= '96569999999')

let field = '965' || field[5,11];

{update des lignes selectionnes}
update table
set
b = field
where a = key_name1;

let compteur = compteur + 1;
end foreach

return compteur;
end

END PROCEDURE;

i use informix V9.40.FC7 version.

get how many rows are updated with dbinfo('sqlca.sqlerrd2')
and discover what key_name1 was not been updated...

Hi, I don't know why your stored procedure doesn't update all the records. Maybe it has to do with the lock level (table locking vs. row locking) in conjunction with the isolation level. Check out under what conditions concerning these matters the SP is executed.
But a simpler solution is to not use the FOREACH loop but make the changes in one query, like:BTW. In your SELECT query you're making use of numeric operators on a string type column. I wouldn't do that, for one thing because it costs extra time fot the implicit conversion the server has to make to interpret the clause. That's why I use the string operator MATCHES here.

Regards

I would add a 'BEGIN WORK' statement at the beginning of your procedure and then add a 'COMIT WORK' statement at the end. This should force all updates to be commited.