When the database application calls dataset methods:

• Insert / Edit / Post / Delete - in immediate updates mode,
• ApplyUpdates - in cached updates mode,

then updating data SQL commands will be automatically generated by AnyDAC. The AnyDAC SQL commands generator is aware of the database identity fields, sequences, triggers, special data types (Oracle BLOB / CLOB / BFILE), pessimistic locking commands, etc. And generates a most efficient SQL command, depending on the connected DBMS. This reduces the number of cases where a developer must use the hand made SQL commands. AnyDAC does not force a developer to use TADUpdateSQL, which may be used to override the update SQL commands. 

For example, when posting a new record to an Oracle table, where ID field is filled by a trigger from a sequence and IMAGE is of BLOB type, AnyDAC will generate the SQL command: 

INSERT INTO OracleTab (NAME, DT, IMAGE)
VALUES (:NEW_NAME, :NEW_DT, EMPTY_BLOB())
RETURNING :NEW_ID, :NEW_IMAGE

1. FROM, INTO and UPDATE phrases 

AnyDAC will use the main (first) table in the SELECT ... FROM ... statement, as an updating table name. Also, this table will be used to retrieve mkPrimaryKeyFields metadata. Use UpdateOptions.UpdateTableName to explicitly specify the updating table. This is required, when:

• a dataset is TADStoredProc;
• TADQuery has not a SELECT query;
• AnyDAC fails to get the updating table name correctly from the query;
• application needs to redirect updates to a specific table.

2. WHERE phrase 

The UpdateOptions.UpdateMode controls the WHERE clause generation for posting updates and deletions. The default value upWhereKeyOnly uses in WHERE phrase only the unique identifying columns and provides most efficient and safe way to locate the updating row. When no unique identifying columns is specified and no row identifying column is found, AnyDAC will switch UpdateOptions.UpdateMode to upWhereAll. And some fields included into WHERE clause may lead to no rows found:

• DOUBLE, FLOAT, TIME, DATETIME fields and other floating point fields may lead to a precision lost at value comparison;
• textual fields may have an invalid encoding or extra spaces leading to unsuccessful comparision;
• other similar failures may happen.

In such cases application gets an exception: 

[anydac][DApt]-400. Update command updated [0] instead of [1] record.

Similarly, when columns in WHERE are not unique identifying a row, more than single record may be updated. Then an exception is: 

[anydac][DApt]-400. Update command updated [4] instead of [1] record.

To resolve these issues consider to:

• provide correct unique identifying columns;
• disable some fields WHERE usage by excluding pfInWhere from corresponding TField.ProviderFlags property;
• suppress these errors by setting UpdateOptions.CountUpdatedRecords to False.

Note, to avoid the above issues with a SQL Server table, which also has triggers, the triggers should have SET NOCOUNT ON specified at the beginning. If that is not possible, then set UpdateOptions.CountUpdatedRecords to False. 

3. SET and VALUES phrases 

The UpdateOptions.UpdateChangedFields controls the fields to include into UPDATE SET ... or INSERT VALUES ... phrases. Setting to True will include only changed fields, what helps:

• to minimize the traffic at updates posting;
• to avoid not-needed constraints validations;
• to avoid extra trigger firings;
• to minimize redo log generation.

Setting to False will include all fields, what helps:

• to reuse the same generated statement for posting all updates and minimize DBMS work to prepare a statements.

To disable updates to some columns exclude pfInUpdate from corresponding TField.ProviderFlags property. 

The UpdateOptions.RefreshMode = rmOnDemand controls automatic refreshing of the column values, which may be changed by the DBMS after inserting or updating a record. The columns, which may require refreshing after record inserting are:

• auto-incrementing columns;
• database calculated columns;
• columns with default values;
• row identifying columns;
• timestamp columns;
• columns updated by a trigger.

The columns, which may require refreshing after record updating are:

• database calculated columns;
• timestamp columns;
• columns updated by a trigger.

Depending on a DBMS features, the additional phrases / commands will be generated, returning the refreshing column values:

• Oracle, Firebird, PostgreSQL - RETURNING phrase;
• DB2 - SELECT ... FROM FINAL TABLE phrase;
• SQL Server, SQL Anywhere, SQLite - SQL batch with additional SELECT command;
• otherwise - an additional SELECT command.

For some DBMS, environment and task the UpdateChangedFields = False may give a better performance. And combined with few other settings as a UpdateOptions.FastUpdates property, it allows to get even better performance at updates posting, by avoiding the additional queries and enabling to cache the generated update commands. 

The FastUpdates = True is equivalent to:

• UpdateChangedFields = False;
• UpdateMode = upWhereKeyOnly;
• LockMode = lmNone;
• RefreshMode = rmManual;
• CheckRequired = False.

Editing the Data, TADUpdateOptions