Automatic generation of update SQL statements

SQLDB (more in particular, TSQLQuery) can automatically generate update statements for the data it fetches. To this end, it will scan the SQL statement and determine the main table in the query: this is the first table encountered in the FROM part of the SELECT statement.

For INSERT and UPDATE operations, the SQL statement will update/insert all fields that have pfInUpdate in their ProviderFlags property. Read-only fields will not be added to the SQL statement. Fields that are NULL will not be added to an insert query, which means that the database server will insert whatever is in the DEFAULT clause of the corresponding field definition.

The WHERE clause for update and delete statements consists of all fields with pfInKey in their ProviderFlags property. Depending on the value of the UpdateMode property, additional fields may be added to the WHERE clause:

upWhereKeyOnly

No additional fields are added: only fields marked with pfInKey are used in the WHERE clause

upWhereChanged

All fields whose value changed are added to the WHERE clause, using their old value.

upWhereAll

All fields are added to the WHERE clause, using their old value.

In order to let SQLDB generate correct statements, it is important to set the ProviderFlags properties correct for all fields.

In many cases, for example when only a single table is queried, and no AS field aliases are used, setting TSQLQuery.UsePrimaryKeyAsKey combined with UpdateMode equal to upWhereKeyOnly is sufficient.

If the automatically generated queries are not correct, it is possible to specify the SQL statements to be used in the UpdateSQL, InsertSQL and DeleteSQL properties. The new field values should be specified using params with the same name as the field. The old field values should be specified using the OLD_ prefix to the field name. The following example demonstrates this:

INSERT INTO MYTABLE 
  (MYFIELD,MYFIELD2)
VALUES
  (:MYFIELD,:MYFIELD2);

UPDATE MYTABLE SET
  MYFIELD=:MYFIELD
  MYFIELD2=:MYFIELD2
WHERE
  (MYFIELD=:OLD_MYFIELD);
  
DELETE FROM MYTABLE WHERE (MyField=:OLD_MYFIELD);

See also