Behaviour is identical in:

/********/
create table demo (id integer not null, something varchar(30), prev
varchar(30));
insert into demo values (1,'a',null);
insert into demo values (2,'b',null);
insert into demo values (3,'c',null);
insert into demo values (4,'d',null);

set term !! ;
create or alter procedure doesntwork as
declare variable id integer;
declare variable something varchar(30);
begin
for select id,something from demo into :id, :something do
begin
update demo outerdemo set outerdemo.prev = (select
d1.something from demo d1 where d1.id = (select max(d2.id) from demo
d2 where d2.id < :id))
where outerdemo.id = :id;
end
end!!
set term ; !!
/********/

NOT EXISTS wouldn't do it for me, I need to know the next higest
value in the table.

Anyway, there may be better ways of doing it... point is either my
sproc is wrong (quite possible!) or it's a FB bug.

Thanks,

John


--- In firebird-support@yahoogroups.com, "Svein Erling"
<svein.erling.tysvaer@k...> wrote:

> I don't know whether it is an error or not, but the first thing to

do

> is to eliminate all sources of potential errors. So try aliasing

your

> tables:
>
> for select id,something from wtreestore into :id, :something do
> begin
> update demo d1 set d1.something =
> (select d2.something from demo d2
> where d2.id = (select max(d3.id) from demo d3
> where d3.id < :id))
> where d1.id = :id;
> end
>
> (I guess that in your where clauses d2.id could be considered d1.id
> and that d3.id could be considered d1.id or d2.id by the engine

when

> omitting the aliases)
>
> If it is an error, I would never have discovered nor noticed it in
> this list since I generally use NOT EXISTS rather than = max.
>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "johnsparrowuk" wrote:
> > Is this a known bug?
> >
> > for select id,something from wtreestore into :id, :something do
> > begin
> > update demo set something =
> > (select something from demo
> > where id = (select max(id) from demo where id < :id))
> > where id = :id;
> > end