UPDATE Statement

Modifies a column's values in existing rows of a table.

Category: Data Manipulation
Supports: EXECUTE Statement
Data source: SAS data set, SPD Engine data set, SPD Server table, Aster, DB2 under UNIX and PC, Greenplum, MDS, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP HANA, Sybase IQ, Teradata
Syntax
Arguments
Details
Comparisons
See Also

Syntax

UPDATE table
{
SET column=<sql-expression> [, ...column=<sql-expression>]
| column=value-expression [, ...column=value-expression]
}
[WHERE <sql-expression> | value-expression];

Arguments

table

specifies a table name.

Restriction A Teradata table must have a primary key defined to be updated with the UPDATE statement.

column

specifies a column name.

<sql-expression>

specifies any valid SQL expression.

See <sql-expression>

value-expression

specifies any valid value expression.

Tip You can use parameter arrays in the INSERT statement.

Details

The UPDATE statement changes the values in all or part of an existing row in a table.
The SET clause specifies which columns to modify and the values to assign them. Columns that are not SET retain their previous values. In the SET clause, a column reference on the left side of the equal sign can also appear as part of the expression on the right side of the equal sign. The SET clause uses the current value of the column in the expression. For example, you could use this expression to give employees a $1,000 holiday bonus. 
update payroll set salary = salary + 1000;
The WHERE clause enables you to choose which rows to update. If you omit the WHERE clause, then all the rows are updated. When you use a WHERE clause, only the rows that meet the WHERE clause condition are updated.
Note: If row-level permissions are in effect for the table, you cannot update rows in the table. Row-level security is available only with SAS Federation Server. For more information about row-level security, see the SAS Federation Server documentation.

Comparisons

The DELETE statement enables you to delete rows from a table. The INSERT statement enables you to insert new rows into a table. The UPDATE statement enables you to change rows in a table.

See Also

Statements:
DELETE Statement
INSERT Statement
WHERE clause in the SELECT Statement
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 23, 2017