UPDATE Statement

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

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

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

table-name(back to Syntax)
     [ owner.]table-name [ [ AS ] correlation-name ]
     | [ owner.]view-name [ [ AS ] correlation-name ]

table-expression(back to Syntax)
   table-spec 
   | table-expression join-type table-specON condition ] 
   | table-expression, ...

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

The table referenced in the UPDATE statement can 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.

  • With ORDER BY T2.c3, T1.c2 is updated to 8.
  • With ORDER BY T2.c3 DESC, T1.c2 is updated to 6.
SAP 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 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.

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

(back to top)

  • SQL—Vendor extension to ISO/ANSI SQL grammar.
  • SAP Sybase Database product—With these exceptions, syntax of the IQ UPDATE statement is generally compatible with the Adaptive Server UPDATE statement Syntax 1: SAP Sybase IQ supports multiple tables with join conditions in the FROM clause.

    Updates of remote tables are limited to SAP Sybase IQ syntax supported by CIS.

Permissions

(back to top)

Requires UPDATE privilege on the columns being modified.

Related concepts
Row Limitation Clauses in SELECT Query Blocks
Related reference
CREATE TABLE Statement