firebird-automations
commented
16 years ago Closed firebird-automations closed 9 years ago
firebird-automations
commented
16 years ago firebird-automations
commented
15 years ago firebird-automations
commented
14 years ago firebird-automations
commented
14 years ago Commented by: @asfernandes
Nickolay, please test it.
firebird-automations
commented
14 years ago firebird-automations
commented
14 years ago firebird-automations
commented
13 years ago Commented by: @livius2
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?
firebird-automations
commented
13 years ago Commented by: @asfernandes
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).
firebird-automations
commented
13 years ago Commented by: @livius2
Delete statement is not priority only <search condition> to remove unnecessary updates and record versions
firebird-automations
commented
13 years ago Commented by: @asfernandes
And why you can't put your <search condition> in the ON clause?
firebird-automations
commented
13 years ago Commented by: @livius2
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?
firebird-automations
commented
13 years ago Commented by: @asfernandes
No, this new syntax is not good. For that there is explicit cursors and PSQL.
firebird-automations
commented
13 years ago Commented by: @livius2
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, ...
firebird-automations
commented
13 years ago Commented by: @asfernandes
> 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.
firebird-automations
commented
9 years ago firebird-automations
commented
9 years ago
Submitted by: @samofatov
Is duplicated by CORE3128 Is related to QA631
Votes: 2
Merge statement is designed to simplify/optimize final step of ETL processing for data warehousing, but when I tried to use it, I found that existing Firebird implementation is nearly impossible to use for this purpose. In the situation when source data is mostly unchanged Firebird's MERGE logic triggers expensive row updates and recalculation of aggregates for unchanged rows.
The request is to implement SQL2008 MERGE syntax with DELETE extension:
<merge statement> ::= MERGE INTO <target table> [ [ AS ] <merge correlation name> ] USING <table reference> ON <search condition> <merge operation specification>
<merge correlation name> ::= <correlation name>
<merge operation specification> ::= <merge when clause>...
<merge when clause> ::= <merge when matched clause> | <merge when not matched clause>
<merge when matched clause> ::= WHEN MATCHED [ AND <search condition> ] THEN <merge update specification>
<merge when not matched clause> ::= WHEN NOT MATCHED [ AND <search condition> ] THEN <merge insert specification>
<merge update specification> ::= UPDATE ... | DELETE
DELETE is the useful extension to standard MERGE syntax supported by DB2.
Commits: FirebirdSQL/firebird@9f16366d2712c30ad7fc9456bf4e445d44c1ccb2 FirebirdSQL/firebird@e0762f5f956910d4bc2bdb3b933f4d0617aae93e FirebirdSQL/fbt-repository@a1a4b7b164885ee6d18a58d562b79a058e97e7c6