UPDATE
来源:互联网 发布:方舟生存进化优化好么 编辑:程序博客网 时间:2024/05/16 13:58
UPDATE
Purpose
Use the UPDATE
statement to change existing values in a table or in the base table of a view or the master table of a materialized view.
Additional Topics
Prerequisites
Syntax
Semantics
Examples
Prerequisites
For you to update values in a table, the table must be in your own schema or you must have the UPDATE
object privilege on the table.
For you to update values in the base table of a view:
You must have the
UPDATE
object privilege on the view, andWhoever owns the schema containing the view must have the
UPDATE
object privilege on the base table.
The UPDATE
ANY
TABLE
system privilege also allows you to update values in any table or in the base table of any view.
You must also have the SELECT
object privilege on the object you want to update if:
The object is on a remote database or
The
SQL92_SECURITY
initialization parameter is set toTRUE
and theUPDATE
operation references table columns, such as the columns in awhere_clause
.
Syntax
update::=
Description of the illustration update.gif
(DML_table_expression_clause::=, update_set_clause ::=, where_clause ::=, returning_clause::=, error_logging_clause::=)
DML_table_expression_clause::=
Description of the illustration DML_table_expression_clause.gif
(subquery::=--part of SELECT, subquery_restriction_clause ::=, table_collection_expression ::=)
subquery_restriction_clause ::=
Description of the illustration subquery_restriction_clause.gif
table_collection_expression ::=
Description of the illustration table_collection_expression.gif
update_set_clause ::=
Description of the illustration update_set_clause.gif
where_clause ::=
Description of the illustration where_clause.gif
returning_clause::=
Description of the illustration returning_clause.gif
error_logging_clause::=
Updating a Table: Examples The following statement gives null commissions to all employees with the job SH_CLERK
:
UPDATE employees SET commission_pct = NULL WHERE job_id = 'SH_CLERK';
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
UPDATE employees SET job_id = 'SA_MAN', salary = salary + 1000, department_id = 120 WHERE first_name||' '||last_name = 'Douglas Grant';
The following statement increases the salary of an employee in the employees
table on the remote
database:
UPDATE employees@remote SET salary = salary*1.1 WHERE last_name = 'Baer';
The next example shows the following syntactic constructs of the UPDATE
statement:
Both forms of the
update_set_clause
together in a single statementA correlated subquery
A
where_clause
to limit the updated rows
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);
The preceding UPDATE
statement performs the following operations:
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
Sets
department_id
for these employees to thedepartment_id
corresponding to Bombay (location_id
2100)Sets each employee's salary to 1.1 times the average salary of their department
Sets each employee's commission to 1.5 times the average commission of their department
Updating a Partition: Example The following example updates values in a single partition of the sales
table:
UPDATE sales PARTITION (sales_q1_1999) s SET s.promo_id = 494 WHERE amount_sold > 1000;
Updating an Object Table: Example The following statement creates two object tables, people_demo1
and people_demo2
, of the people_typ
object created inTable Collections: Examples. The example shows how to update a row of people_demo1
by selecting a row from people_demo2
:
CREATE TABLE people_demo1 OF people_typ;CREATE TABLE people_demo2 OF people_typ;UPDATE people_demo1 p SET VALUE(p) = (SELECT VALUE(q) FROM people_demo2 q WHERE p.department_id = q.department_id) WHERE p.department_id = 10;
The example uses the VALUE
object reference function in both the SET
clause and the subquery.
Correlated Update: Example For an example that uses a correlated subquery to update nested table rows, please refer to "Table Collections: Examples".
Using the RETURNING Clause During UPDATE: Example The following example returns values from the updated row and stores the result in PL/SQL variables bnd1
, bnd2
, bnd3
:
UPDATE employees SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140 WHERE last_name = 'Jones' RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3;
The following example shows that you can specify a single-set aggregate function in the expression of the returning clause:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1;
90. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS
tables.You want to update the CITY column of the DEPT table for all the rows
with the corresponding value in the CITY column of the LOCATIONS table for
each department.Which SQL statement would you execute to accomplish the task?
A. UPDATE dept d SET city = ANY (SELECT city FROM locations l);
B. UPDATE dept d SET city = (SELECT city FROM locations l)
WHERE d.location_id = l.location_id;
C. UPDATE dept d SET city = (SELECT city FROM locations l WHERE d.location_id
= l.location_id);
D. UPDATE dept d SET city = ALL (SELECT city FROM locations l
WHERE d.location_id = l.location_id);
Answer: C
- update
- update
- update
- update
- update
- update
- update
- update
- update
- update
- update
- update
- Update()
- Update
- update
- UPDATE
- UPDATE
- update
- POJ2230-还是简单的欧拉回路求解
- eclipse的快捷键大全
- 嵌入式软件开发-----Qt setPen()画笔函数的初步了解
- 学习感悟(一)
- linux C++ 资料集合
- UPDATE
- Cocoa 框架概述
- 文件流到 读取和写入
- 碰到“error LNK2019: 无法解析的外部符号”收藏
- spring的官方网站
- xml文件类,封装了IXMLDocument(C++Builder)
- C语言运算符优先级汇总
- HDU 2192 MagicBuilding
- 【转】PPP数据帧的格式