Although AnyDAC is able to automatically generate updating SQL commands, in some cases this can not be done correctly:

Note, the above is not a must, instead check first, what really happens. 

AnyDAC can detect a base table and column names for an aliased or joined columns:

In some cases application needs to specify alternative DB table name, to which the updates will be posted. To do that set UpdateOptions.UpdateTableName to a required table name. 

In some cases application needs to exclude columns from an updating SQL command. To do that exclude pfInUpdate from TField.ProviderFlags. And set TField.ReadOnly to prohibit field value modification. 

To specify alternative DB column name, set TField.Origin to a required value. 

The TADUpdateSQL component allows to:

• selectively override the update SQL commands generated by AnyDAC;
• enable updates posting, when AnyDAC cannot generate the update commands, like a complex SELECT's or stored procedure calls.

In general, the TADUpdateSQL is a collection of SQL update commands, each of them handles specific task, like a inserting a new record into database. The TADUpdateSQL may be setup at design-time and / or at run-time. 

To setup at design-time, drop TADUpdateSQL on a form. Set dataset UpdateObject property to point to this component. Then double click on TADUpdateSQL to invoke the AnyDAC Update SQL Editor: 

The editor will automatically get the updating table name from the associated dataset and setup columns according to the dataset columns TField.ProviderFlags and TField.AutoGenerateValue. 

To change setup, use:

• the "Table Name" combo to specify updating table name;
• the "Key Fields" list box to specify unique identifying columns (corresponds to pfInKey in ProviderFlags);
• the "Updating Fields" list box to specify columns to include into update (corresponds to pfInUpdate in ProviderFlags);
• the "Refreshing Fields" list box to specify columns those values must be refreshed after posting update (corresponds to AutoGenerateValue <> arNone).

The "Describe From DB" button provides setup for the specified table, by retrieving its metadata from the database. The "Revert To Defaults" button provides setup using the dataset field properties. The additional options may be specified on the "Options" page. 

When setup is finished press the "Generate SQL" button to generate full set of the updating SQL's. Also, the editor may be used to manually edit update SQL commands on the "SQL Commands" page. 

Press OK to store changes in the TADUpdateSQL. 

To specify the TADUpdateSQL SQL commands at run-time, the application should use XxxxSQL properties. To reference in a SQL to a specific column value use the parameter markers:

• new column value - ':NEW_<column name>';
• old column value - ':OLD_<column name>';
• current column value - ':<column name>'.

These parameter values will be assigned automatically by AnyDAC. Do not assign a value to them, because their values will be overridden. AnyDAC will ignore parameters with other names. 

The command parameters and command macros may be setup only at run-time. For that you have to use TADUpdateSQL.Commands property, which returns references to the TADCommand objects. For example: 

ADUpdateSQL1.InsertSQL.Text := 'insert into &tab (id, name) values (:new_id, :new_name)';
ADUpdateSQL1.Commands[arInsert].Macros[0].AsRaw := 'Orders';

This event allows to completely override posting updates from a dataset. Also, you can combine TADUpdateSQL's and OnUpdateRecord approaches, to enable semi-automatic updates posting to different tables or databases. 

For details see OnUpdateRecord event description and the AnyDAC demos:

• AnyDAC\Samples\Comp Layer\TADQuery\CachedUpdates\OnUpdateRecord
• AnyDAC\Samples\Comp Layer\TADUpdateSQL\Main

Update Command Generation, TADUpdateSQL, OnUpdateRecord