[DERBY-3155] Support for SQL:2003 MERGE statement

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_triage10_10
• Urgency: Normal
• Issue & fix info: Patch Available
• Assignee: Richard N. Hillegas
• Reporter: Trejkaz
• Created: 28/Oct/07 22:49
• Updated: 09/Sep/16 09:51
• Resolved: 29/May/14 16:33

Description

A relatively common piece of logic in a database application is to check for a row's existence and then either update or insert depending on its existence.

SQL:2003 added a MERGE statement to perform this operation. It looks like this:

MERGE INTO table_name USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT column1 [, column2 ...] VALUES (value1 [, value2 ...])

At the moment, the only workaround for this would be to write a stored procedure to do the same operation, or to implement the logic client-side.

Issue Links

• is related to: DERBY-6011 Derby performs very badly (seems to deadlock and timeout) in very simple multi-threaded tests
• is related to: DERBY-6414 Incorrect handling when using an UPDATE to SET an identity column to DEFAULT
• is related to: DERBY-6429 Privilege checks for UPDATE statements are wrong.
• is related to: DERBY-6652 Compilation error using a view as the source data set for a MERGE statement
• is related to: DERBY-6428 Adding an update trigger to a table causes Derby to require overbroad update privileges
• is related to: DERBY-6522 Make LOB handling by MERGE statement more efficient.
• is related to: DERBY-6656 Re-enable views as the source data sets of MERGE statements
• is related to: DERBY-6723 Allow derived table as a source in MERGE statement
• is related to: DERBY-6460 Allow subqueries in the WHEN [ NOT ] MATCHED clauses of MERGE statements.
• is related to: DERBY-6474 Add support for synonyms in MERGE statements
• is related to: DERBY-6476 Allow derived column lists in MERGE statements.
• is related to: DERBY-6526 Document the MERGE statement
• relates to: DERBY-6689 Assert failure/NPE when using ROW_NUMBER in MERGE ... INSERT