FirebirdSQL logo

Using an alias

If you assign an alias to a table or a view, the alias must be used when specifying columns and also in any column references included in other clauses.

Example

Correct usage:

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Not possible:

update Fruit F set Fruit.soort = 'pisang' where ...

The SET Clause

In the SET clause, the assignment expressions, containing the columns with the values to be set, are separated by commas.In an assignment expression, column names are on the left and the values or expressions to assign are on the right.A column may be assigned only once in the SET clause.

A column name can be used in expressions on the right.The old value of the column will always be used in these right-side values, even if the column was already assigned a new value earlier in the SET clause.

Using the expression DEFAULT will set the column to its default value (either NULL or the value specified on the DEFAULT clause of the column definition).For an identity column, specifying DEFAULT will generate a new identity value.It is possible to “update” calculated columns in the SET clause if and only if the assigned value is DEFAULT.

Note

It is not possible to assign DEFAULT as a parameter value.

Here is an example

Data in the TSET table:

A B
---
1 0
2 0

The statement:

UPDATE tset SET a = 5, b = a;

will change the values to:

A B
---
5 1
5 2

Notice that the old values (1 and 2) are used to update the b column even after the column was assigned a new value (5).

The WHERE Clause

The WHERE clause sets the conditions that limit the set of records for a searched update.

In PSQL, if a named cursor is being used for updating a set, using the WHERE CURRENT OF clause, the action is limited to the row where the cursor is currently positioned.This is a positioned update.

Note

To be able to use the WHERE CURRENT OF clause in DSQL, the cursor name needs to be set on the statement handle before executing the statement.

Examples
UPDATE People
  SET firstname = 'Boris'
  WHERE lastname = 'Johnson';

UPDATE employee e
  SET salary = salary * 1.05
  WHERE EXISTS(
         SELECT *
           FROM employee_project ep
           WHERE e.emp_no = ep.emp_no);

UPDATE addresses
  SET city = 'Saint Petersburg', citycode = 'PET'
  WHERE city = 'Leningrad'

UPDATE employees
  SET salary = 2.5 * salary
  WHERE title = 'CEO'

For string literals with which the parser needs help to interpret the character set of the data, the introducer syntax may be used.The string literal is preceded by the character set name, prefixed with an underscore character:

-- notice the '_' prefix

UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

The ORDER BY and ROWS Clauses

The ORDER BY and ROWS clauses make sense only when used together.However, they can be used separately.

If ROWS has one argument, m, the rows to be updated will be limited to the first m rows.

Points to note
  • If m > the number of rows being processed, the entire set of rows is updated

  • If m = 0, no rows are updated

  • If m < 0, an error occurs and the update fails

If two arguments are used, m and n, ROWS limits the rows being updated to rows from m to n inclusively.Both arguments are integers and start from 1.

Points to note
  • If m > the number of rows being processed, no rows are updated

  • If n > the number of rows, rows from m to the end of the set are updated

  • If m < 1 or n < 1, an error occurs and the update fails

  • If n = m - 1, no rows are updated

  • If n < m -1, an error occurs and the update fails

ROWS Example
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;