> But what about this extending many WHEN MATCHED statement - this is very useful and follow by merge specification - and i suppose is simple to implement.

Seems you're right about this. And this should be very useful, specially related to my saying about current unusefulness of WHEN MATCHED ... DELETE.

Yes you have right for executing any script - better is explicit cursor - and then we can use also variables.

But what about this extending many WHEN MATCHED statement - this is very useful and follow by merge specification - and i suppose is simple to implement.

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*0.5, TARGET.FIELD2 = 4;
WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*0.7, TARGET.FIELD2 = 8;
WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = TARGET.FIELD1*1.2, TARGET.FIELD2 = 7;
WHEN NOT MATCHED [ AND <search condition> ] THEN
INSERT ...;

this is better and faster then doing this in this way
if we are limited to only one chck WHEN MATCHED

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
UPDATE SET TARGET.FIELD = CASE WHEN ... THEN TARGET.FIELD1*0.5 WHEN ... THEN TARGET.FIELD1*0.7 WHEN .. THEN TARGET.FIELD1*1.2 END, CASE WHEN .. THEN TARGET.FIELD2 = 4 WHEN .. THEN TARGET.FIELD2 = 8; WHEN .. THEN TARGET.FIELD2 = 7 END
WHEN NOT MATCHED [ AND <search condition> ] THEN
INSERT ...;

###########
this is simple situation but in complicated situation your code is not readable and complicated if e.g you must in some case update Field1, but in other only field 2 , Field3, ...

No, this new syntax is not good. For that there is explicit cursors and PSQL.

Because i do not test only Target_field=Source_field but also Target_field<>Source_field
when i put this into on then i got unnecessary inserts


#######################################################
and i have also proposition to relax merge clause to this format

MERGE

..
ON ..

WHEN MATCHED [ AND <search condition> ] THEN
..
WHEN NOT MATCHED [ AND <search condition> ] THEN
..
WHEN MATCHED [ AND <search condition> ] THEN
..
WHEN NOT MATCHED [ AND <search condition> ] THEN

######################################################################################
above is equivalent to case scenario and i suppose that this is still conform sql standard but do extension for it

MERGE

..
ON ..
CASE
WHEN MATCHED [ AND <search condition> ] THEN
BEGIN
..
END
WHEN NOT MATCHED [ AND <search condition> ] THEN
BEGIN

END
WHEN MATCHED [ AND <search condition> ] THEN
BEGIN

END
WHEN NOT MATCHED [ AND <search condition> ] THEN
BEGIN

END

###################################################################

and inside "block"
you can do any script operation like
MERGE
...
ON (...)
WHEN MATCHED [ AND <search condition> ] THEN
INSERT (...) values(...);
UPDATE SET TARGET_FIELD=xxx;
/* but also */
UPDATE SOME_TABLE ST SET ST.X=.. WHERE ST.Y=...;

WHEN NOT MATCHED [ AND <search condition> ] THEN
and the same here

.....

###################################################################

above work like execute block to do any more tasks then only update/insert/delete on target table
but still do optimisations to updates target table in one loop cursor

is this good concept or you see some problems here?
If yes - should i post this as new ticket to tracker?

And why you can't put your <search condition> in the ON clause?

Delete statement is not priority
only <search condition> to remove unnecessary updates and record versions

I think the current DELETE extension is not very useful the way it was done.

I really think we should still revise it, so I would not even try to make it in 2.5 now.

Oracle MERGE satisfies my needs, although it's very strange in some aspects (with UPDATE + DELETE).

Is possible to see this Fix also in 2.5 series not only in FB3 (long long time to wait for stable version of FB3)
Or fix is too complicated to backport?

Nickolay, please test it.