Update Statement

The Update statement is used to change rows in a database file. The form of the Update statement supported for flat-file drivers is:

UPDATE table_name SET col_name = expr, ...

[ WHERE { conditions | CURRENT OF cursor_name } ]

table_name can be a simple table name or a full path name. A table name is preferred for portability to other SQL data sources.

col_name is the name of a column whose value is to be changed. Several columns can be changed in one statement.

expr is the new value for the column. The expression can be a constant value or a subquery. Character string values must be enclosed with single (’) or double (") quotation marks, date values must be enclosed by braces {}, and logical values that are letters must be enclosed by periods (for example, .T. or .F.). Subqueries must be enclosed in parentheses.

The Where clause (any valid clause described in “Select Statement”) determines which rows are to be updated.

The Where Current Of cursor_name clause can be used only by developers coding directly to the ODBC API. It causes the row at which cursor_name is positioned to be updated. This is called a "positioned update." You must first execute a Select...For Update statement with a named cursor and fetch the row to be updated.

An example of an Update statement on the emp table is:

UPDATE emp SET salary=32000, exempt=1

WHERE emp_id = 'E10001'

The Update statement changes every record that meets the conditions in the Where clause. In this case, the salary and exempt status are changed for all employees having the employee ID E10001. Because employee IDs are unique in the emp table, only one record is updated.

An example using a subquery is:

UPDATE emp SET salary = (SELECT avg(salary) FROM emp)

WHERE emp_id = 'E10001'

In this case, the salary is changed to the average salary in the company for the employee having employee ID E10001.