New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support SQL 2008 syntax for MERGE statement with DELETE extension [CORE2005] #2442
Comments
Modified by: @dyemanovsummary: Support SQL 2008 syntax for MERGE statment => Support SQL 2008 syntax for MERGE statement |
Modified by: @dyemanovassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @asfernandessummary: Support SQL 2008 syntax for MERGE statement => Support SQL 2008 syntax for MERGE statement with DELETE extension |
Commented by: @asfernandes Nickolay, please test it. |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Alpha 1 [ 10331 ] |
Modified by: @asfernandes |
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) |
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). |
Commented by: @livius2 Delete statement is not priority |
Commented by: @asfernandes And why you can't put your <search condition> in the ON clause? |
Commented by: @livius2 Because i do not test only Target_field=Source_field but also Target_field<>Source_field ####################################################### MERGE .. WHEN MATCHED [ AND <search condition> ] THEN ###################################################################################### MERGE .. END END END ################################################################### and inside "block" WHEN NOT MATCHED [ AND <search condition> ] THEN ..... ################################################################### above work like execute block to do any more tasks then only update/insert/delete on target table is this good concept or you see some problems here? |
Commented by: @asfernandes No, this new syntax is not good. For that there is explicit cursors and PSQL. |
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 .. WHEN MATCHED [ AND <search condition> ] THEN this is better and faster then doing this in this way MERGE .. WHEN MATCHED [ AND <search condition> ] THEN ########### |
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. |
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: 9f16366 e0762f5 FirebirdSQL/fbt-repository@a1a4b7b
The text was updated successfully, but these errors were encountered: