|
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.
|
Seems you're right about this. And this should be very useful, specially related to my saying about current unusefulness of WHEN MATCHED ... DELETE.