I. syntax and Principles of the update Statement 1. Syntax single table: Update table name set column name = new value where column name = A value such as: Update t_join_situationset
Join_state = '1' whereyear = '000000' the join_state field of the Data updated for the Year "2011" is "1 ". If the updated field is indexed, the index will be re-created during the update, and the update efficiency will be slow. Associate multiple tables and update the field values of one table to the fields in another table: Update table a set. field 1 = (select B. field 1 from Table B where. field 2 = B. field 2) where exists (select 1 from Table B where. field 2 = B. field 2) the Oracle update statement is not as easy to write as MSSQL. Even if it is written, it may be reported during execution because multiple rows of data are found in the sub-query of set, oracle requires one-to-one data update, so an error is prompted. To solve this problem, we must ensure that the retrieved values correspond one to one. 2. principle the principle of the update statement is to first find the data based on the where condition. If a subquery exists in the Set, execute the subquery to find the value and assign it to the updated field for update. For example, update table a set a. Field 1 = (select B. Field 1 from Table B where a. Field 2 = B. Field 2)
Where exists (select 1 from Table B where a. Field 2 = B. Field 2 ). Query all the data in Table A and recyclically check whether the data conforms to exists (select 1 from Table B where. field 2 = B. field 2) condition. If yes, execute (select B. field 1 from Table B where. field 2 = B. field 2) query, find the corresponding value update. field 1. When updating the joined table, you must have exists (select 1 from Table B where. field 2 = B. field 2). Otherwise, Field 1 of other data in Table A is updated to null. Reference http://chenxy.blog.51cto.com/729966/811672
