UPDATE - SQL Command

  • Article

Updates records in a table with new values.

UPDATE [DatabaseName1!]TableName1 SET Column_Name1 = eExpression1
   [, Column_Name2 = eExpression2 ...]
   WHERE FilterCondition1 [AND | OR FilterCondition2 ...]

Parameters

  • [DatabaseName1!]TableName1
    Specifies the table in which records are updated with new values.

    DatabaseName1! specifies the name of a non-current database containing the table. You must include the name of the database containing the table if the database is not the current one. Include the exclamation point (!) delimiter after the database name and before the table name.

  • SET Column_Name1 = *eExpression1   *[, Column_Name2 = eExpression2
    Specifies the columns that are updated and their new values. If you omit the WHERE clause, every row in the column is updated with the same value.

  • WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
    Specifies the records that are updated with new values.

    FilterCondition specifies the criteria that records must meet to be updated with new values. You can include as many filter conditions as you like, connecting them with the AND or OR operator. You can also use the NOT operator to reverse the value of a logical expression, or use EMPTY( ) to check for an empty field.

Remarks

UPDATE - SQL can only update records in a single table. Note that subqueries are supported in UPDATE – SQL.

You can determine the number of records updated by checking the value of the _TALLY system variable immediately after the UPDATE - SQL command.

Unlike REPLACE, UPDATE - SQL uses record locking when updating multiple records in a table opened for shared access. This reduces record contention in multiuser situations, but may reduce performance. For maximum performance, open the table for exclusive use or use FLOCK( ) to lock the table.

Example

The following example opens the customer table in the testdata database. UPDATE -SQL is used to set all of the values in the maxordamt field to 25.

CLOSE DATABASES

OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer     && Open customer table

* Set and display amounts for customers
UPDATE customer SET maxordamt = 25
BROWSE FIELDS company,maxordamt

See Also

DELETE - SQL | GATHER | INSERT - SQL | REPLACE | SCATTER | _TALLY