Oracle update from

Many friends familiar with SQL server often make a mistake when they come into contact with oracle, that is, they always think that oracle also has the structure of update from. The from statement does not exist in the update Statement of oracle.

We have briefly introduced the update Statement of Oracle. Here we will focus on updating the update syntax of the current table based on another table.

We know that there is a schema named scott in the example database in oracle, and there is a table named emp in it. Let's take the table here as an example.

First, create a table emp1 with the same structure and insert some data for it.

Create table emp1

As

Select * from emp where deptno = 20;

Update part of emp1 data

Update emp1

Set sal = sal + 100,

Comm = nvl (comm, 0) + 50

Then we try to use the data in emp1 to update the sal and comm columns in emp.

We can write it like this.

Update emp

Set (sal, comm) = (select sal, comm. From emp1 where emp. empno = emp1.empno)

Where exists (select 1 from emp1 where emp1.empno = emp. empno)

Pay special attention to the where clause here. You can try not to write the where clause to execute the following sentence, and you will make many values in emp empty.

This is because if the where clause is not written in the oracle update statement, oracle will update all values by default, even if you use a subquery and the value cannot be found in the subquery, you will assume that oracle may skip these values. You are wrong, oracle will update the value of this row to null.

We can also write as follows:

Update (select a. sal asal, B. sal bsal, a. comm acomm, B. comm bcomm from emp a, emp1 B where a. empno = B. empno)

Set asal = bsal,

Acomm = bcomm;

The table here is a Class View.

Of course, you may encounter the following errors during execution:

ERROR is located in row 2nd:

ORA-01779: The columns corresponding to the non-key save table cannot be modified

This is because the new table emp1 does not have a primary key.

Add a primary key

Alter table emp1

Add constraint pk_emp1 primary key (empno );

After execution

The preceding statement can be successfully executed.

Here we will summarize:

There is no update from structure in oracle, so there are two solutions to the problem of updating the value of this table from another table:

One is to use subqueries. When using subqueries, you must pay attention to the where condition (usually followed by the exists clause). Unless the two tables correspond one to one, the where condition is indispensable, when the where condition is omitted, a large number of null values may be inserted.

The other is the update method of the Class View, which is also unique to oracle. First, extract all the corresponding data, and then update the data in the same way as the update table. Note that the unique data type of the table must be guaranteed.

 

This article is from the "corner of Java Lin sui" blog