It will be nice if we can do something like this:
merge into fbl_row t
using (select :vdid vdid, :vpid vpid, :vqty vqty, :vcost vcost from rdb$database ) s
on (t.did=s.vdid and t.pid=s.vpid)
when matched then
update set pqty = pqty + s.vqty, rcnt = rcnt+1
when NOT matched then insert (id, did, pid, pqty, pcost)
values(gen_id(gen_test2,1), s.vdid, s.vpid, s.vqty, s.vcost);
returning id
into :vrow;
As far as I understand current version of FB does not support it ? (
http://firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb210-merge)
is it possible to add RETURNING clause not only to INSERT statement (i.e in the "when NOT matched" branch) but also to UPDATE one (into "when MATCHED") ?