[FB-Tracker] Created: (CORE-2005) Support SQL 2008 syntax for MERGE statment

Support SQL 2008 syntax for MERGE statment
------------------------------------------

                 Key: CORE-2005
                 URL: http://tracker.firebirdsql.org/browse/CORE-2005
             Project: Firebird Core
          Issue Type: Improvement
          Components: Engine
    Affects Versions: 2.1.0
            Reporter: Nickolay Samofatov
            Priority: Minor


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.


--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[LacaK]

As we have here new FR, I think, you can close my previous FR
http://tracker.firebirdsql.org/browse/CORE-758
which contains same request and is no longer relevant ;-)
-Laco.

> <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.
>
>

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Leyne, Sean]

Laco,

> As we have here new FR, I think, you can close my previous FR
> http://tracker.firebirdsql.org/browse/CORE-758
> which contains same request and is no longer relevant ;-)
> -Laco.
>
> > <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.

My reading of the case suggests that you are not discussing the same
issue.


Sean


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[asfernandes]

Leyne, Sean escreveu:
> My reading of the case suggests that you are not discussing the same
> issue.
He later commented on the ticket for change MERGE. They seems identical
now, so CORE-758 could be closed.


Adriano


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Leyne, Sean]

> Leyne, Sean escreveu:
> > My reading of the case suggests that you are not discussing the same
> > issue.
> He later commented on the ticket for change MERGE. They seems
identical
> now, so CORE-758 could be closed.

Done


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel