UPDATE statement

Description

Modifies existing rows of a single table, or a view that contains only one table.

Syntax

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

FROM table-expression
table-expression:
table-spec | table-expression join-type table-spec [ ON condition ] | table-expression, ...

Examples

Example 1

• Transfers employee Philip Chin (employee 129) from the sales department to the marketing department:

UPDATE employee
SET dept_id = 400
WHERE emp_id = 129 ;

Example 2

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

UPDATE employee
SET bonus = base * 6/100
WHERE dept_id=400;

Example 3

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

UPDATE employee
SET emp.salary = emp.salary + dept.bonus
FROM employee emp, department dept
WHERE emp.deptnum = dept.deptnum;

Example 4

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

UPDATE employee
SET emp.salary = emp.salary + dept.bonus
FROM employee emp JOIN department dept
ON emp.deptnum = dept.deptnum;

Usage

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

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.

• With ORDER BY T2.c3, T1.c2 is updated to 8.

• With ORDER BY T2.c3 DESC, T1.c2 is updated to 6.

Sybase IQ rejects any UPDATE statement in which the table being updated is on the null-supplying side of an outer join. In other words:

• In a left outer join, the table on the left side of the join cannot be missing any rows on joined columns.

• In a right outer join, the table on the right side of the join cannot be missing any rows on joined columns.

• In a full outer join, neither table can be missing any rows on joined columns.

For example, in the following 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.

None.

Standards