INSERT, UPDATE, and DELETE Options

The following query engine hints are available for INSERT, UPDATE and DELETE. These options are specified right after the INSERT, UPDATE and DELETE keywords.

INSERT, UPDATE, DELETE Option

Description

Syntax

Example

CASE_SENSITIVE

CASE_SENSITIVE forces string comparisons to be case-sensitive. This option overrides the TDV Server’s Case Sensitivity configuration setting (under TDV Server > SQL Engine > SQL Language).

If CASE_SENSITIVE is set to FALSE or not specified, TDV Server’s Case Sensitivity configuration setting determines how string comparisons are evaluated.

CASE_SENSITIVE[={"TRUE"|"FALSE"}]

 
UPDATE {OPTION CASE_SENSITIVE="TRUE"} table1
	SET column1 = ’BAR’

WHERE column1 = ’FOO’

CHECK_VIEW_CONSTRAINTS

CHECK_VIEW_CONSTRAINTS makes TDV Server preserve the data integrity of the view definition; in other words, it prevents changes to the view.

If CHECK_VIEW_CONSTRAINTS is not specified, TDV Server does not preserve the data integrity of the view definition.

Suppose a view V1 is defined as follows:

SELECT column1 FROM table1 WHERE column1 = 5

Suppose also that someone then tries to update V1 with the following update statement:

UPDATE V1
SET column1 = 5
WHERE column1 = 6

The UPDATE statement fails if CHECK_VIEW_CONSTRAINTS was specified, because a row with value column1=6 is outside the bounds of the definition of the view V1.

 
CHECK_VIEW_CONSTRAINTS

 

 
UPDATE {OPTION CHECK_VIEW_CONSTRAINTS} table1
	SET column1 = ’BAR ’

WHERE column1 = ’FOO ’

IGNORE_TRAILING_SPACES

IGNORE_TRAILING_SPACES causes comparisons to ignore trailing spaces. This option overrides the TDV Server’s Ignore Trailing Spaces configuration setting (under TDV Server > SQL Engine > SQL Language).

If IGNORE_TRAILING_SPACES is set to FALSE or not specified, TDV Server’s Ignore Trailing Spaces configuration setting determines how string comparisons are evaluated.

IGNORE_TRAILING_SPACES[={"TRUE"|"FALSE"}]

 
UPDATE {OPTION IGNORE_TRAILING_SPACES="FALSE"} table1
	SET column1 = ’BAR ’

WHERE column1 = ’FOO ’

STRICT

STRICT prevents the query engine from pushing aspects of SQL (such as mathematical and string functions, and the Oracle POSITION function) to the underlying data source when the source does not adhere to strict SQL 92 behavior. This could affect performance. If STRICT is not specified, the query engine relaxes SQL 92 rules to achieve more push.

strict

 
UPDATE {OPTION STRICT} table1
	SET column2 = ’S’

WHERE SIN(column1) = 1