UPDATE
Use the UPDATE command to replace values in specific table columns. You cannot update columns that are used as distribution keys for a table.
Syntax
Syntax for using the
UPDATE command:
UPDATE <table> SET <col> = <expression>[,<expression>…]
[ FROM <fromlist> ]
[ WHERE <condition> ]Inputs
The UPDATE command takes the following inputs:
| Input | Description |
|---|---|
| <table> | The name of the table in which rows are to be
updated. You can use a table alias. For example: |
| <col> | The name of a column in a table. |
| <expression> | Specifies a valid expression or value to assign to the column. |
| <fromlist> | Specifies columns from other tables for the WHERE condition. When you use the FROM clause, the inner join is implicit and the join condition must be specified in the WHERE clause. If outer joins are required, you might require subselects or staging/temporary tables to specify the necessary join conditions. |
| <condition> | Specifies a WHERE condition. |
Output
The UPDATE command
has the following output:
| Output | Description |
|---|---|
| UPDATE <number> | The command was successful, and <number> represents the number of rows updated (this might be zero). |
Privileges
You must be the admin user, the table owner, the owner of the database or schema where the table resides, or your account must have the Update privilege for the table or for the Table object class.
Usage
The following provides
sample usage.
- Change the word Drama to Dramatic within the column
kind:MYDB.SCH1(USER)=> UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; MYDB.SCH1(USER)=> SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama'; code | title | did | date_prod | kind | len -------+---------------+-------+-------------+------------+------ BL101 | The Third Man | 101 | 1949-12-23 | Dramatic | 01:44 P_302 | Becket | 103 | 1964-02-03 | Dramatic | 02:28 M_401 | War and Peace | 104 | 1967-02-12 | Dramatic | 05:57 T_601 | Yojimbo | 106 | 1961-06-16 | Dramatic | 01:50