UPDATE Statement

Syntax

UPDATE table
... SET [column-name  = expression, …
... [ FROM table-expression, ]
... [ WHERE search-condition ]
... [ ORDER BY expression [ ASC | DESC ] , …]

FROM table-expression

Parameters

• table-expression – table-spec | table-expression join-type table-spec [ ON condition ] | table-expression, …

Examples

• Example 1 – Transfer employee Philip Chin (employee 129) from the sales department to the marketing department:

  UPDATE Employees
  SET DepartmentID = 400
  WHERE EmployeeID = 129;

• Example 2 – The Marketing Department (400) increases bonuses from 4% to 6% of each employee’s base salary:

  UPDATE Employees
  SET bonus = base * 6/100
  WHERE DepartmentID =400;

• Example 3 – Each employee gets a pay increase with the department bonus:

  UPDATE Employees
  SET emp.Salary = emp.Salary + dept.bonus
  FROM Employees emp, Departments dept
  WHERE emp.DepartmentID = dept.DepartmentID;

• Example 4 – Another way to give each employee a pay increase with the department bonus:

  UPDATE Employees
  SET emp.salary = emp.salary + dept.bonus
  FROM Employees emp JOIN Departments dept
  ON emp.DepartmentID = dept.DepartmentID;

Usage

The table on which you use UPDATE may be a base table or a temporary table.

Defaults on updates are honored for current user, user and current timestamp, and timestamp only.

Each named column is set to the value of the expression on the right-hand side of the equal sign. Even column-name can be used in the expression—the old value is used.

The FROM clause can contain multiple tables with join conditions and returns all the columns from all the tables specified and filtered by the join condition and/or WHERE condition.

Using the wrong join condition in a FROM clause causes unpredictable results. If the FROM clause specifies a one-to-many join and the SET clause references a cell from the “many” side of the join, the cell is updated from the first value selected. In other words, if the join condition causes multiple rows of the table to be updated per row ID, the first row returned becomes the update result. For example:

UPDATE T1 
SET T1.c2 = T2.c2
FROM T1 JOIN TO T2
ON T1.c1 = T2.c1

If table T2 has more than one row per T2.c1, results might be as follows:

T2.c1              T2.c2              T2.c3

1                  4                  3

1                  8                  1

1                  6                  4

1                  5                  2

With no ORDER BY clause, T1.c2 may be 4, 6, 8, or 9.

For example, in this statement, table T1 is on the left side of a left outer join, and thus cannot contain be missing any rows:

UPDATE T1 
SET T1.c2 = T2.c4
FROM T1 LEFT OUTER JOIN T2
ON T1.rowid = T2.rowid

Normally, the order in which rows are updated does not matter. However, in conjunction with the NUMBER(*) function, an ordering can be useful to get increasing numbers added to the rows in some specified order. If you are not using the NUMBER(*) function, avoid using the ORDER BY clause, because the UPDATE statement performs better without it.

In an UPDATE statement, if the NUMBER(*) function is used in the SET clause and the FROM clause specifies a one-to-many join, NUMBER(*) generates unique numbers that increase, but do not increment sequentially due to row elimination.

You can use the ORDER BY clause to control the result from an UPDATE when the FROM clause contains multiple joined tables.

SAP Sybase IQ ignores the ORDER BY clause in searched UPDATE and returns a message that the syntax is not valid ANSI syntax.

If no WHERE clause is specified, every row is updated. If you specify a WHERE clause, SAP Sybase IQ updates only rows satisfying the search condition.

The left side of each SET clause must be a column in a base table.

Views can be updated provided the SELECT statement defining the view does not contain a GROUP BY clause or an aggregate function, or involve a UNION operation. The view should contain only one table.

Character strings inserted into tables are always stored in the case they are entered, regardless of whether the database is case-sensitive or not. Thus a character data type column updated with the string 'Value' is always held in the database with an uppercase V and the remainder of the letters lowercase. SELECT statements return the string as 'Value.' If the database is not case-sensitive, however, all comparisons make 'Value' the same as 'value,' 'VALUE,' and so on. The IQ server may return results in any combination of lowercase and uppercase, so you cannot expect case-sensitive results in a database that is case-insensitive (CASE IGNORE). Further, if a single-column primary key already contains an entry 'Value,' an INSERT of 'value' is rejected, as it would make the primary key not unique.

If the update violates any check constraints, the whole statement is rolled back.

SAP Sybase IQ supports scalar subqueries within the SET clause, for example:

UPDATE r
SET r.o= (SELECT MAX(t.o) 
FROM t ... WHERE t.y = r.y),
r.s= (SELECT SUM(x.s) 
FROM x ... 
WHERE x.x = r.x)
WHERE r.a = 10

SAP Sybase IQ supports DEFAULT column values in UPDATE statements. If a column has a DEFAULT value, this DEFAULT value is used as the value of the column in any UPDATE statement that does not explicitly modify the value for the column.

See CREATE TABLE Statement for details about updating IDENTITY/AUTOINCREMENT columns, which are another type of DEFAULT column.

Standards

Permissions

Requires UPDATE privilege on the columns being modified.