Oracle 10g update operation

1. Update a single table

Syntax: update test set column = value [, column = value]... [where condition]

Eg: select * from test

Eg: update test set sex = 111 Translation: update the sex column of the entire table to 111

Eg: update test set sex = 333 where AAA = 7: update a value in the test table

2. Multi-Table update is also called subquery update.

Eg: update the values of the sal column to the same value as the emp table. If you use a regular update statement, you must first check the values of the SAL column in the emp table, use the update statement to update the query result.

Two steps are required, and additional data I/O is required. Using subqueries can effectively reduce I/O and improve execution efficiency.

Update test set sal = (select sal from emp) -- the error message is "one row subquery returns multiple rows ".

Update test set sal = (select sal from emp where rownum = 1) Translation: All sal columns are updated to a value.

Update test set sal = (select sal from (select * from (select rownum r, sal from emp) where r = 16) where AAA = 8: update the sal value with the condition AAA = 8

The above update statement is divided into three steps

1. select * from (select rownum r, sal from emp) where r = 16

2. select sal from (select * from (select rownum r, sal from emp) where r = 16)

3. assign values

Select * from test where AAA = 8