Thanks in advance,

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?

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

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

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.
with CTE as
(
select gen_id(gen_new,0) as num , Elements.elm_prof, Elements.elm_mat From Elements
}
update Elements e set e.prop_type= CTE.num where E.elm_mat = CTE.elm_mat

BTW: I use Firebird 2.1 ; SQL editor is : FlameRobin

>
> set generator gen_new to 0;
>
> with
>
> select gen_id(gen_new, -gen_id(gen_new, 0)) from rdb$database
>
> This will also reset the generator to 0, is DML and should will never stop working.

I'll bear that in mind, it makes sense for repetitive calls which could be very nuisance to spot, but for now I'm deadly stuck to run the query at least once.