UPDATE SQL Statement

The supported syntax is:

UPDATE <table> SET <assign-list> WHERE <search-exp>

Each document in an application defines both a table and a view. The table name is the name of the document, and the view name is "Simple View of " followed by the document name. In addition, each application contains a table called MASTER and a corresponding "Simple view of MASTER". A view name can be used anywhere a table name is. Column names are the fields defined for a document.

Only a single table can be specified.

The WHERE clause is always optional, and if not specified all rows are affected. Sub-queries are not supported in the WHERE clause, but arbitrary Boolean expressions can be used to select rows based on the values found in the row, as in "SELECT name, score from form1 where score > 20 and (grade = 6 or (grade = 5 and teacher = ‘williams’))".

You can use the document number instead of the document name. This is not as much an optimization as a convenience considering the document titles are typically verbose. Keep these rules in mind:

• Document number must be specified in decimal format like 1, 2, 5 and so on.
• Document number 0 maps to the Master document.
• Do not use leading zeros for the document number.
• Specifying an invalid document number will cause a SQL parse error.
• It is not usually a great practice to hardwire document numbers.

Assign-list is a list of fields to be updated, of the form field = value [, field = value]

Values can be simple literal (often called scalar) values, such as 'vanilla', they can be the results of expressions such as 10 + 20, or they can involve other fields in the same record, see below.

UPDATE statements can refer to the existing values in a row, so an expression such as "update form1 set totscore = subscore1 + subscore2" is perfectly valid (so long as form1 is a valid table name containing columns totscore, subscore1 and subscore2, and that the three fields support arithmetic). NCS Header fields may not be specified in an UPDATE statement, though any update to a record will set the Altered Since Scanned flag in the NCS Header to indicate that the record has been changed since scanning.

See also

SQL Statements Accepted by the Data Provider

SQL Best Practices