update的一些注意的语法

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm#i2073065

 

update_set_clause::=

Description of the illustrationupdate_set_clause.gif

 

update_set_clause

The update_set_clause lets you setcolumn values.

column

Specify the name of a column of the object that is to beupdated. If you omit a column of the table from theupdate_set_clause, then thevalue of that column remains unchanged.

If column refers to a LOB objectattribute, then you must first initialize it with a value of emptyor null. You cannot update it with a literal. Also, if you areupdating a LOB value using some method other than a directUPDATE SQL statement, then you must first lock the rowcontaining the LOB. See for_update_clause for moreinformation.

If column is part of thepartitioning key of a partitioned table, then UPDATEwill fail if you change a value in the column that would move therow to a different partition or subpartition, unless you enable rowmovement. Please refer to the row_movement_clause of CREATE TABLE or ALTER TABLE.

In addition, if column is part of thepartitioning key of a list-partitioned table, thenUPDATE will fail if you specify a value for the columnthat does not already exist in the partition_value list of one ofthe partitions.

subquery

Specify a subquery that returns exactly one row for each rowupdated.

• If you specify only one column in the update_set_clause, then thesubquery can return only one value.

• If you specify multiple columns in the update_set_clause, then thesubquery must return as many values as you have specifiedcolumns.

• If the subquery returns no rows, then the column isassigned a null.

• --如果子查询中没返回任何行，那么列值将被分配为空。

• If this subquery refers to remoteobjects, then the UPDATE operation can run in parallelas long as the reference does not loop back to an object on thelocal database. However, if the subquery in theDML_table_expression_clauserefers to any remote objects, then the UPDATEoperation will run serially without notification.

You can use the flashback_query_clause withinthe subquery to update table with past data. Pleaserefer to the flashback_query_clause ofSELECT for more information on this clause.