Description:
The command is used to update data in existing tables in the database.
Syntax:
UPDATE [ hint ]
{ dml_table_expression_clause
| ONLY (dml_table_expression_clause)
} [ t_alias ]
update_set_clause
[ where_clause ]
[ returning_clause ]
[error_logging_clause]
DML_table_expression_clause::=
{ [ schema. ]
{ table
[ partition_extension_clause
| @ dblink
]
| { view | materialized view } [ @ dblink ]
}
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}
partition_extension_clause::=
{ PARTITION (partition)
| PARTITION FOR (partition_key_value [, partition_key_value]...)
| SUBPARTITION (subpartition)
| SUBPARTITION FOR (subpartition_key_value [, subpartition_key_value]...)
}
subquery_restriction_clause ::=
WITH { READ ONLY
| CHECK OPTION
} [ CONSTRAINT constraint ]
table_collection_expression ::=
TABLE (collection_expression) [ (+) ]
update_set_clause ::=
SET
{ { (column [, column ]...) = (subquery)
| column = { expr | (subquery) | DEFAULT }
}
[, { (column [, column]...) = (subquery)
| column = { expr | (subquery) | DEFAULT }
}
]...
| VALUE (t_alias) = { expr | (subquery) }
}
where_clause ::=
WHERE condition
returning_clause::=
{ RETURN | RETURNING } expr [, expr ]...
INTO data_item [, data_item ]...
error_logging_clause::=
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
example
UPDATE employees
SET commission_pct = NULL
WHERE job_id = 'SH_CLERK';
UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';
UPDATE employees@remote
SET salary = salary*1.1
WHERE last_name = 'Baer';
UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);