The UPDATE Statement - Firebird

The UPDATE statement is used for changing the values in columns in existing rows of tables. It can also operate on tables through cursor sets and updatable views. SQL does not allow a single UPDATE statement to target rows in multiple tables.

An UPDATE query that modifies only the current row of a cursor is known as a positioned update. One that may update multiple rows is known as a searched update.

Positioned vs. Searched Operations

UPDATE and DELETE statements may be positioned (targeted at one and only one row) or searched (targeted at zero or more rows). Strictly speaking, a positioned update can occur only in the context of the current row of a cursor operation, while a searched update, optionally limited by the search conditions in a WHERE clause, occurs in all other contexts.

Most dataset component interfaces emulate the positioned update or delete by using a searched update with a uniquely targeted WHERE clause. These unidirectional or scrollin dataset classes maintain a “current row buffer” that stores or links to the column and key values for the row that the user task has selected for an operation. When
the user is ready to post an UPDATE or DELETE request, the component constructs a searched UPDATE or DELETE statement that targets one database row uniquely by using the primary key (or some other unique column list) in the WHERE clause.

Using the UPDATE Statement

The UPDATE statement has the following general form:

UPDATE table-name | view-name SET column-name = value [,column-name = value ...] [WHERE <search conditions> | WHERE CURRENT OF cursor-name]

For searched updates, if a WHERE clause is not specified, the update will be performed on every row in the table.

The SET Clause

The syntax pattern for the SET clause is

SET column-name = value [,column-name = value ...]

The SET clause is a comma-separated list that identifies each column for modification, along with the new value to be assigned to it. The new value must be of the correct type and size for the column’s definition. If a column is nullable, it can also be set to NULL instead of a value.

A value can be

  • A constant value of the correct type (e.g., SET COLUMNB = '99'). If the column is a character type of a specified character set that is different from the character set of the connection, an update value can be forced to that special character set by including the appropriate character set introducer to the left of the constant string. A character set introducer is the character set name prefixed with an underscore symbol, for example:
    SET COLUMNY = _ISO8859_1 'fricassée'
  • The identifier of another column in the same table, provided it is of the correct type. For example, SET COLUMNB = COLUMNX will replace the current value of COLUMNB with the current value of COLUMNX. A character set introducer (see the previous item) can be used if appropriate.
  • An expression. For example, SET REVIEW_DATE = REVIEW_DATE + 14 updates a date column to 2 weeks later than its current value. For details about expressions, refer to the next chapter.
  • A server context variable (e.g., SET DATE_CHANGED = CURRENT_DATE).
  • A parameter placeholder symbol appropriate to the syntax implemented in the application code (e.g., from Delphi, SET LAST_NAME = :LAST_NAME, or from another application interface, SET LAST_NAME = ?).
  • An SQL or user-defined function (UDF) call. For example, SET BCOLUMN = UPPER(ACOLUMN) uses the internal SQL function UPPER to transform the value of ACOLUMN to uppercase and store the result in BCOLUMN. For information about using functions, refer to the next chapter.

A COLLATE clause can be included when modifying character (but not BLOB) columns, if appropriate. For most character sets, it would be necessary to use one in the previous example, since the default (binary) collation sequence does not usually support the UPPER() function. For example, if ACOLUMN and BCOLUMN are in character set ISO8859_1, and the language is Spanish, the SET clause should be

SET BCOLUMN = UPPER(ACOLUMN) COLLATION ES_ES

Value Switching

Take care when “switching” values between columns. A clause like

... SET COLUMNA = COLUMNB

will cause the current value in COLUMNA to be overwritten by the current value in COLUMNB immediately. If you then do

SET COLUMNB = COLUMNA

the old value of COLUMNA is gone and the value of COLUMNB and COLUMNA will stay the same as they were after the first switch.

To switch the values, you would need a third column in which to “park” the value of COLUMNA until you were ready to assign it to COLUMNB:

... SET COLUMNC = COLUMNA, COLUMNA = COLUMNB, COLUMNB = COLUMNC ...

You can use expressions with SET so, if switching two integer values, it is possible to “work” the switch by performing arithmetic on the two values. For example, suppose COLUMNA is 10 and COLUMNB is 9:

... SET COLUMNB = COLUMNB + COLUMNA, /* COLUMNB is now 19 */ COLUMNA = COLUMNB - COLUMNA, /* COLUMNA is now 9 */ COLUMNB = COLUMNB - COLUMNA /* COLUMNB is now 10 */ ...

Always test your assumptions when performing switch assignments!

Updating BLOB Columns

Updating a BLOB column actually replaces the old BLOB with a completely new one. The old BLOB_ID does not survive the update. Also

  • It is not possible to update a BLOB by concatenating another BLOB or a string to the existing BLOB.
  • A text BLOB can be set using a string as input. For example, MEMO in the next example is a text BLOB:
UPDATE A TABLE SET MEMO = 'Friends, Romans, countrymen, lend me your ears: I come not to bury Caesar, but to praise him.';

Updating ARRAY Columns

In embedded applications (ESQL), it is possible to construct a pre-compiled SQL statement to pass slices of arrays to update (replace) corresponding slices in stored arrays.

It is not possible to update ARRAY columns in DSQL at all. To update arrays, it is necessary to implement a custom method in application or component code that calls the API function isc_array_put_slice.

Column Defaults

Column DEFAULT constraints are never considered when UPDATE statements are processed.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics