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

• Transfer 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) will increase bonus 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 her department bonus.

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

Example 4

• Here’s 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 UPDATE statement is used to modify rows of a single table or view that contains only one table. The table may be a base table or a temporary table. The base table cannot be part of any join index. 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 will be 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.

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

T2.c1              T2.c2              T2.c3

1                  4                  3

1                  8                  1

1                  6                  4

1                  5                  2

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 must not be missing any rows on joined columns.

• In a right outer join, the table on the right side of the join must not 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