Skip to main content

Datometry Documentation

Check for Duplicate UPDATE Statements

The Hyper-Q hint update_duplicate_check lets you check for duplicate records in UPDATE statements.

The update_duplicate_check query hint overlaps in functionality with the passthru_upd_with_join hint.

  • If both hints are in use, the hint defined with the narrower scope takes precedence.

  • If both hints are defined within the same scope, update_duplicate_check takes precedence over passthru_upd_with_join.

    For example, if update_duplicate_check is specified as a global setting in the Hyper-Q configuration file (dtm.ini), but passthru_upd_with_join is set to true at the query level, no duplicate check is performed because the narrower query scope disables duplicate checks.

To learn more about Hyper-Q SQL hints and scope precedence, see About Hyper-Q SQL Hints

To learn more about the hint passthru_upd_with_join, see Materialize JOIN Predicates for Updates to Temporary Tables.

The syntax for update_duplicate_check is:

update_duplicate_check = none | M1 | M2

where:

Parameter

Description

none

Turns off duplicate checking.

M1

Specifies that:

  • When using an EQUI JOIN the duplicate check is performed using a JOIN.

  • When using a JOIN which is not an EQUI JOIN, the duplicate check is performed using a subquery (a SQL query nested inside a larger query).

M1 is the default setting if no option is specified.

M2

Specifies that a when using UPDATE with a non-equality predicate between the target and the source tables, that a LEFT [ OUTER ] JOIN perform the duplicate check.

Hint Scope

The update_duplicate_check hint can be specified at the request, session, and global level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 49. Specify that Hyper-Q Perform Duplicate Checks as a Global Setting

You can specify update_duplicate_check as a global setting in the Hyper-Q configuration file (dtm.ini). In the example shown below, update_duplicate_check is enabled using the M1 setting.

"gateway".update_duplicate_check = M1

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.



Example 50. Specify Duplicate Checks as a Query Hint for a Request

In the following example table_1 is the target table, and table_2 and table_3 are the source tables for the UPDATE. A non-equality predicate between the target and the source tables specifies which rows are to be modified, and update_duplicate_check uses a LEFT [ OUTER ] JOIN to perform the duplicate check using the M2 option.

When using update_duplicate_check as a query hint, you must specify if it is to be applied at the request or session level. This example specifies that it be applied at the request level using the for request specifier.

UPDATE table_1 FROM tgt table_1, src table_2, src table_3 SET j=table_2.j 
--@dtm_update_duplicate_check=M2 for request
WHERE table_1.i<table_2.i OR table_1.i + 10 > table_3.i;

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.



Example 51. Specify Duplicate Checks as a Query Hint for a Session

In the following example  table_1 is the target table, and table_2  is the source table for the update. This example specifies that the update_duplicate_check hint be applied at the session level using the for session specifier.

UPDATE table_1
FROM database_name.CUSTOMER_BILL table_1,
database_name.SALES table_2
--@dtm_update_duplicate_check=M2 for session
SET AC_ID=5 
WHERE table_1.SSID=5
AND table_2.SSID=5
AND (
	NVL(table_2.ATTRIBUTE20,'')<>'FFS' 
	OR table_2.SALES_SKEY=5 
	OR table_2.AC_ID=5
)
AND DECODE(table_1.ORG_ID,NULL,cast(5 as numeric(18,0)),table_1.SALES_SKEY) = table_2.SALES_SKEY 
AND 5 = 5;