[Reprint] Oracle update statements Version 10.2

Basic update statements
Update all records	Update <table_name> Set <column_name >=< value>
	Create Table Test Select object_name, object_type From all_objects; Select distinct object_name From test; Update Test Set object_name = 'opops '; Select distinct object_name From test; Rollback;
Update a specific record	Update <table_name> Set <column_name >=< value> Where <column_name >=< value>
	Select distinct object_name From test; Update Test Set object_name = 'load' Where object_name = 'dual '; Commit; Select distinct object_name From Test
Update based on a single queried Value	Update <table_name> Set <column_name> =( Select <column_name> From <table_name Where <column_name> <condition> <value>);
	Create Table Test as select table_name from all_tables; alter table test Add (lower_name varchar2 (30 )); select * from test where table_name like '% A % '; Update test T set lower_name = ( select distinct lower (table_name) from all_tables A where. table_name = T. table_name and. table_name like '% A %' ) ; commit; select * from test;
Update Based on a query returning multiple values	Update <table_name> <alias> Set(<Column_name>, <column_name>)=( Select(<Column_name>, <column_name>) From <table_name> Where <alias. column_name >=< alias. column_name>);
	Create Table Test Select T. table_name, T. tablespace_name, S. extent_management From user_tables T, user_tablespaces s Where T. tablespace_name = S. tablespace_name And 1 = 2; Desc Test Select * from test; -- Does not work Update Test Set (table_name, tablespace_name) = ( Select table_name, tablespace_name From user_tables ); -- Works Insert into test (Table_name, tablespace_name) Select table_name, tablespace_name From user_tables; Commit; Select * From Test Where table_name like '% A % '; -- Does not work Update test T Set tablespace_name, extent_management = ( Select tablespace_name, extent_management From user_tables A, user_tablespaces u Where T. table_name = A. table_name And a. tablespace_name = U. tablespace_name And T. table_name like '% A % '); -- Does not works Update test T Set(Tablespace_name, extent_management)= ( Select distinct U. tablespace_name, U. extent_management From user_tables A, user_tablespaces u Where T. table_name = A. table_name And a. tablespace_name = U. tablespace_name And T. table_name like '% A % '); Rollback; -- Works Update test T Set(Tablespace_name, extent_management)= ( Select distinct U. tablespace_name, U. extent_management From user_tables A, user_tablespaces u Where T. table_name = A. table_name And a. tablespace_name = U. tablespace_name) Where T. table_name like '% A % '; Commit; Select * From test;
Update the results of a SELECT statement	Update (<SELECT statement>) Set <column_name >=< value>;
	Select * From Test Where table_name like '% A % '; Select * From Test Where table_name not like '% A % '; Update ( Select * From Test Where table_name not like '% A % ') Set extent_management = 'unknown '; Select * From test;
Correlated update
Single Column	Update table (<SELECT statement>) <alias> Set <column_name> = ( Select <column_name> From <table_name> <alias> Where <alias. table_name >=< alias. table_name>;
	Conn HR/hr Create Table empbak Select * from employees; Update employees Set salary = salary * 1.1; Commit; Update employees T1 Set salary = ( Select salary From empbak T2 Where t1.employee _ id = t2.employee _ id );
Multi-Column	Update table (<SELECT statement>) <alias> Set <column_name >=< value>;
	Update Table1 t_alias1 Set (col1, col2) = ( Select col1, col2 From Table2 t_alias2 Where t_alias1.col3 = t_alias2.col3 );
Nested table update
	See nested tables page
Update with returning clause
Returning clause demo	Update (<SELECT statement>) Set ....;
	Conn HR/hr VaR bnd1 number VaR bnd2 varchar2 (30) VaR bnd3 number Update employees Set job_id = 'sa _ man ', salary = salary + 1000, Departtment_id = 140 Where last_name = 'Jones' Returning salary * 0.25, last_name, department_id Into: bnd1,: bnd2,: bnd3; Print bnd1 Print bnd2 Print bnd3
	Conn HR/hr Variable bnd1 number Update employees Set salary = salary * 1.1 Where department_id = 100 Returning sum (salary) into: bnd1; Print bnd1
Update object table
Update a table object	Update (<SELECT statement>) Set ....;
	Update Table1 p set value (P) = (Select value (q) from Table2 Q where P. ID = Q. ID) Where p. ID = 10;
Record Update
Update Based on a record	Update <table_name> Set ROW = <record_name>;
	Create Table T Select table_name, tablespace_name From all_tables; Select distinct tablespace_name From t; Declare Trec t % rowtype; Begin TREC. table_name: = 'dual '; TREC. tablespace_name: = 'new _ tbsp '; Update t Set ROW = TREC Where table_name = 'dual '; Commit; End; / Select distinct tablespace_name From t;
Update partitioned table
Update only records in a single Partition	Update <table_name> partition <partition_name> Set <column_name >=< value> Where <expression>;
	Update sales partition (sales_q00002005) S Set S. promo_id = 494 Where amount_sold> 9000;