Introduction

---

The SQL UPDATE statement is used to modify one or more existing rows in a table.

Syntax

---

UPDATE table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]

SET column_ref = update_value
[, column_ref = update_value...]

[FROM table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE]

[[INNER | [LEFT | RIGHT] OUTER JOIN] table_reference
[AS correlation_name | correlation_name] [EXCLUSIVE] ON join_condition]

[WHERE predicates]

[COMMIT [INTERVAL commit_interval] [FLUSH]]

[NOJOINOPTIMIZE]
[JOINOPTIMIZECOSTS]
[NOWHEREJOINS]

Use the UPDATE statement to modify one or more column values in one or more existing rows in a single table per statement. Use a table reference in the UPDATE clause to specify the table to receive the data changes. Use the EXCLUSIVE keyword to specify that the table should be opened exclusively.

SET Clause

---

The SET clause is a comma-separated list of update expressions for the UPDATE statement. The syntax is as follows:

SET column_ref = update_value
[, column_ref = update_value...]

Each expression comprises the name of a column, the assignment operator (=), and the update value for that column. The update values in any one update expression may be literal values or calculated values.

FROM and JOIN Clauses

---

You may use an optional FROM clause with additional JOIN clauses to specify multiple tables from which an UPDATE statement retrieves data for the purpose of updating the target table. The value for a FROM clause is a comma-separated list of table names, with the first table exactly matching the table name specified after the UPDATE clause. Specified table names must follow DBISAM's SQL naming conventions for tables. Please see the Naming Conventions topic for more information. The following UPDATE statement below updates data in one table based upon a LEFT OUTER JOIN condition to another table:

UPDATE orders SET ShipToContact=Customer.Contact
FROM orders LEFT OUTER JOIN customer
ON customer.custno=orders.custno

You can use the AS keyword to specify a table correlation name, or alternately you can simply just specify the table correlation name after the source table name. The following example uses the second method to give each source table a shorter name to be used in qualifying source columns in the query:

UPDATE orders o SET ShipToContact=c.Contact
FROM orders o LEFT OUTER JOIN customer c
ON c.custno=o.custno

Use the EXCLUSIVE keyword to specify that the table should be opened exclusively.


The table reference cannot be passed to a FROM clause via a parameter. Please see the SELECT Statement topic for more information.

WHERE Clause

---

The WHERE clause specifies filtering conditions for the UPDATE statement. The syntax is as follows:

WHERE predicates

Use a WHERE clause to limit the effect of a UPDATE statement to a subset of rows in the table, and the clause is optional.

The value for a WHERE clause is one or more logical expressions, or predicates, that evaluate to TRUE or FALSE for each row in the table. Only those rows where the predicates evaluate to TRUE are modified by an UPDATE statement. For example, the UPDATE statement below modifies all rows where the State column contains a value of 'CA':

UPDATE SalesInfo
SET TaxRate=0.0825
WHERE (State='CA')

Subqueries are supported in the WHERE clause. A subquery works like a search condition to restrict the number of rows updated by the outer, or "parent" query. Such subqueries must be valid SELECT statements. SELECT subqueries cannot be correlated in DBISAM's SQL, i.e. they cannot refer to columns in the outer (or "parent") statement.

Column correlation names cannot be used in filter comparisons in the WHERE clause. Use the actual column name.

Columns devoid of data contain NULL values. To filter using such column values, use the IS NULL predicate.

The UPDATE statement may reference any table that is specified in the UPDATE, FROM, or JOIN clauses in the WHERE clause.

COMMIT Clause

---

The COMMIT clause is used to control how often DBISAM will commit a transaction while the UPDATE statement is executing and/or whether the commit operation performs an operating system flush to disk. The UPDATE statement implicitly uses a transaction if one is not already active. The default interval at which the implicit transaction is committed is based upon the record size of the table being updated in the query and the amount of buffer space available in DBISAM. The COMMIT INTERVAL clause is used to manually control the interval at which the transaction is committed based upon the number of rows updated, and applies in both situations where a transaction was explicitly started by the application and where the transaction was implicitly started by DBISAM. In the case where a transaction was explicitly started by the application, the absence of a COMMIT INTERVAL clause in the SQL statement being executed will force DBISAM to never commit any of the effects of the SQL statement and leaves this up to the application to handle after the SQL statement completes. The syntax is as follows:

COMMIT [INTERVAL nnnn] [FLUSH]

The INTERVAL keyword is optional, allowing the application to use the default commit interval but still specify the FLUSH keyword to indicate that it wishes to have the transaction commits flushed to disk at the operating system level. Please see the Transactions and Buffering and Caching topics for more information.

NOJOINOPTIMIZE Clause

---

The NOJOINOPTIMIZE clause causes all join re-ordering to be turned off for a SELECT statement. The syntax is as follows:

NOJOINOPTIMIZE

Use a NOJOINOPTIMIZE clause to force the query optimizer to stop re-ordering joins for a SELECT statement. In certain rare cases the query optimizer might not have enough information to know that re-ordering the joins will result in worse performance than if the joins were left in their original order, so in such cases you can include this clause to force the query optimizer to not perform the join re-ordering.

JOINOPTIMIZECOSTS Clause

---

The JOINOPTIMIZECOSTS clause causes the optimizer to take into account I/O costs when optimizing join expressions. The syntax is as follows:

JOINOPTIMIZECOSTS

Use a JOINOPTIMIZECOSTS clause to force the query optimizer to use I/O cost projections to determine the most efficient way to process the conditions in a join expression. If you have a join expression with multiple conditions in it, then using this clause may help improve the performance of the join expression, especially if it is already executing very slowly.

Please see the Updating Tables and Query Result Sets topic for more information on updating records in a table.

NOWHEREJOINS Clause

---

The NOWHEREJOINS clause causes the optimizer to treat any join expressions in the WHERE clause (SQL-89-style joins) as normal, un-optimized expressions instead of inner joins. The syntax is as follows:

NOWHEREJOINS

Use a NOWHEREJOINS clause to force the query optimizer to treat any joins in the WHERE clause as normal, un-optimized expressions instead of inner joins. This is very useful when you need the conditions for filtering the results, but do not want to treat them as inner joins because they exhibit a low cardinality (there are lot of matching values). Join conditions with a low cardinality can be slow because they cause a lot of overhead in processing the sets of rows in the DBISAM engine.