FirebirdSQL logo
 Data Definition Language (DDL)Procedural SQL (PSQL) 

Support for WHEN NOT MATCHED BY SOURCE in the MERGE statement

Adriano dos Santos Fernandes

Tracker ticket: #6681

Syntax
<merge when> ::=
		<merge when matched> |
		<merge when not matched by target> |
		<merge when not matched by source>

<merge when not matched by target> ::=
		WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ] THEN
			INSERT [ <left paren> <column list> <right paren> ]
				VALUES <left paren> <value list> <right paren>

<merge when not matched by source> ::=
		WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
			{ UPDATE SET <assignment list> | DELETE }

<merge when not matched by target> is called when a source record matches no record in target.INSERT will change the target table.

<merge when not matched by source> is called when a target record matches no record in source.UPDATE or DELETE will change the target table.

Example
MERGE
	INTO customers c
	USING new_customers nc
	ON (c.id = nc.id)
	WHEN MATCHED THEN
		UPDATE SET name = nc.name
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE

Support multiple rows for DML RETURNING

Adriano dos Santos Fernandes

Tracker ticket: #6815

In DSQL, the RETURNING clause is now able to return multiple rows for DML statements than can affect multiple rows.

See compatibility notes on RETURNING for more information.