SQL Procedure

UPDATE Statement

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

Restriction:	You cannot use UPDATE on a table that is accessed by an engine that does not support UPDATE processing.
See:	Updating Data in a PROC SQL Table

Syntax

Required Arguments

Details

Syntax

UPDATE table-name|sas/access-view|proc-sql-view <AS alias>

SET column=sql-expression <, … column=sql-expression>

< column=sql-expression <, … column=sql-expression>>

<WHERE sql-expression>;

Required Arguments

alias

assigns an alias to table-name, sas/access-view, or proc-sql-view.

column

specifies a column in table-name, sas/access-view, or proc-sql-view.

sas/access-view

specifies a SAS/ACCESS view.

sql-expression

See sql-expression

Restriction

You cannot use a logical operator (AND, OR, or NOT) in an expression in a SET clause.

table-name

specifies a PROC SQL table. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

proc-sql-view

specifies a PROC SQL view. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

Details

You can update one or more rows of a table through a view, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views.

Any column that is not modified retains its original values, except in certain queries using the CASE expression. See CASE Expression for a description of CASE expressions.
To add, drop, or modify a column's definition or attributes, use the ALTER TABLE statement, described in ALTER TABLE Statement.
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. For example, you could use this expression to give employees a $1,000 holiday bonus:
```
   set salary=salary + 1000
```
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 condition are updated.
When you update a column and an index has been defined for that column, the values in the updated column continue to have the index defined for them.