> Although Svein is almost always right about everything, in
> this case he's overgeneralized a good warning. Several
> things in the database metadata have limits on the number of
> times they can be modified, including the number of
> generators you can define, but resetting a generator leaves
> no traces and can be done as often as you wish.

Thanks for putting me right, Ann. I've believed this (incorrectly) for years.

> First of all, I couldn't clearly digest your code the syntax
> I mean, the code right after the using clause starts as CTE
> but after that expression, comes comma and newtbl_2 very
> strange for me , can that syntax be used in Firebird?

new_tbl2 is simply a second CTE table. There's nothing wrong in having several CTE tables after each other, you can even use WITH RECURSIVE and have some non-recursive CTEs amongst them.

> The code is working but this time fills the prop_type of
> Elements with generator initialized value (gen_id(gen_new,0)),
> that is all with zeros . The num field in table "a" is
> disregarded. Where the generator

Why do you use gen_id(gen_new,0) and not gen_id(gen_new,1)?

The generator is put into Newtbl2.num and used like this:

merge into elements as e
using (with newtbl1 ...
Newtbl2 (num, ...) as
(select gen_id(gen_new,1),...
From new_tbl1 n1)
Select n2.num, ...
From newtbl2 n2) a...
when matched then update set e.prop_type = a.num

> Ins't there any intrinsinc function in Firebird other than
> generator to number the rows? variables etc...

Well, SEQUENCE is an alternative to GENERATOR, but I don't think there's much difference between them.

> I don't have to stick with "merge into" other alternatives are
> wellcomed as well, and really don't understand what's wrong
> with that query.

In your case I'd say

EXECUTE BLOCK
as
declare i int = 0;
declare prof int;
declare mat int;
begin
FOR SELECT DISTINCT elm_prof, elm_mat
FROM ELEMENTS into :prof, :mat do
begin
i=i+1;
UPDATE ELEMENTS
SET PROP_TYPE = :i
WHERE ELM_PROF = :prof
AND ELM_MAT = :mat;
end
end

would be a good alternative (could possibly be made even more efficient by using a CURSOR). Another alternative would be something like

UPDATE ELEMENTS E
SET PROP_TYPE =
(SELECT COUNT(DISTINCT CAST(E2.ELM_PROF AS CHAR(9)) ||' '||
CAST(E2.ELM_MAT AS CHAR(9)))
FROM ELEMENTS E2
WHERE E.ELM_PROF > E2.ELM_PROF
OR (E.ELM_PROF = E2.ELM_PROF
AND E.ELM_MAT >= E2.ELM_MAT))
WHERE E.ELM_PROF IS NOT NULL
AND E.ELM_MAT IS NOT NULL

HTH,
Set