Issue Details (XML | Word | Printable)

Key: CORE-3128
Type: Improvement Improvement
Status: Closed Closed
Resolution: Duplicate
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Extent/Improvement in merge - support check operation in update clause

Created: 08/Sep/10 11:33 AM   Updated: 16/Mar/11 05:23 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None

Issue Links:
Duplicate
 


 Description  « Hide
now merge operation look like
MERGE
INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET
name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (cd.id, cd.name)

will be good if we can check field values before update/or not update
example

MERGE
INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
IF (name<>cd.name) THEN /* here is modification in merge statement */
UPDATE SET
name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (cd.id, cd.name)

This modification reduced the number of versions of record
because we only update record when the condition is met

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 08/Sep/10 11:43 AM
Accordingly to the SQL specification, this should look this way:

WHEN MATCHED AND (NAME <> CD.NAME) THEN UPDATE ...

Adriano dos Santos Fernandes added a comment - 08/Sep/10 12:14 PM
This is already done in FB 3. See CORE-2005.