Or (if he wants a number starting from 1 for each master, as opposed to
a unique number for each master):
execute block
as
declare mId integer;
declare mOld integer;
declare dId integer;
declare dmId integer;
begin
m_Old = -1;
dmId = 1;
for select id, master_id
from detail
order by master_id, id
into :dId, :mid
do
begin
if (mid <> mOld) then
begin
dmid = 1;
mOld = mid;
end
else
dmid = dmid + 1;
update detail
set detail_id=:dmId
where id = :dId;
end
end
This can also be done in a simpler update statement, but that is slower
and doesn't scale well over millions of records.
HTH,
Set
