[#CORE-3639] Allow the use of multiple WHEN MATCHED / NOT MATCHED clauses in MERGE, as per the SQL 2008 specification

Firebird Core

Allow the use of multiple WHEN MATCHED / NOT MATCHED clauses in MERGE, as per the SQL 2008 specification

Created: 20/Oct/11 02:53 PM Updated: 01/Jun/15 06:39 PM

Component/s:

Engine

Affects Version/s:

None

Fix Version/s:

3.0 Alpha 1

QA Status:

Done successfully

Test Details:

« Hide

Verified that one may use MERGE which contains up to 254 WHEN-conditions (with trivial statements like: when NOT matched and s.id = 254 then insert values(s.id, s.x) or when matched and s.id = 254 then update set t.x = t.x + s.x).

::: NB :::
JFYI: Statement MERGE ... RETURNING will fail always if SOURCE contain more than 1 row. Even if MERGE condition doesn not affected more than one record in TARGET.
Discussion (rus): sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1159066&msg=17706227

Description

« Hide

Combined with WHEN conditions, this makes MERGE very powerful.

All

Comments

Change History

Subversion Commits

Sort Order:

Adriano dos Santos Fernandes made changes - 20/Oct/11 02:53 PM

Field	Original Value	New Value
Assignee		Adriano dos Santos Fernandes [ asfernandes ]

Adriano dos Santos Fernandes made changes - 23/Oct/11 01:31 AM

Status	Open [ 1 ]	Resolved [ 5 ]
Fix Version/s		3.0 Alpha 1 [ 10331 ]
Resolution		Fixed [ 1 ]

Pavel Zotov made changes - 29/May/15 11:37 AM

Status	Resolved [ 5 ]	Resolved [ 5 ]
QA Status		Done successfully

Pavel Zotov made changes - 01/Jun/15 06:39 PM

Status	Resolved [ 5 ]	Resolved [ 5 ]
Test Details		Verified that one may use MERGE which contains up to 254 WHEN-conditions (with trivial statements like: when NOT matched and s.id = 254 then insert values(s.id, s.x) or when matched and s.id = 254 then update set t.x = t.x + s.x). ::: NB ::: JFYI: Statement MERGE ... RETURNING will fail always if SOURCE contain more than 1 row. Even if MERGE condition doesn not affected more than one record in TARGET. Discussion (rus): sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1159066&msg=17706227

Pavel Zotov made changes - 01/Jun/15 06:39 PM

Status

Resolved [ 5 ]

Closed [ 6 ]