Update and delete for Oracle multiple table associations

Because Oracle does not support the update or delete from statement, Oracle's Multiple Table association update and delete must be supported by subqueries, and, similarly, Oracle does not support simultaneous update or delete multiple tables, typically using the following:

Multiple Table Association Update

First, the test table and data are constructed as follows:

Sys@test16> CREATE table Testa as select Owner,table_name,status from Dba_tables;  
      
Table created.  
Sys@test16> CREATE TABLE Testb as select Owner,object_name,status from Dba_objects;  
      
Table created.

1) Update the status= ' VALID ' of the Testa table with the associated condition Testa.owner=testb.owner and Testa.table_name=testb.table_name

Update Testa a set status= ' VALID '

where exists (select 1 from Testb b where A.owner=b.owner and a.table_name=b.object_name);

2 Update the status of the Testa table is equal to the status of the Testb table, the related conditions ditto

Update Testa A

Set a.status= (select B.status from Testb b where A.owner=b.owner and A.table_name=b.object_name)

where exists (select 1 from Testb b where A.owner=b.owner and a.table_name=b.object_name);

Note here that the above statement succeeds only if the subquery returns only 1 or 0 data at a time, and if more than 1 data is returned, the following error occurs:

Ora-01427:single-row subquery returns more than one row

At this point, you have to qualify the number of return bars in the subquery, such as rownum=1 or distinct.

Here the where exists words are not to be omitted, otherwise will update full table! Please be careful here.

If you look at the execution plan, you will see that the above UPDATE statement will scan the TESTB table two times, if the table is larger, the performance will be affected, if you want to scan only once, you can use the following method instead:

Update Testa A

Set A.STATUS=NVL (select B.status from Testb b where A.owner=b.owner and A.table_name=b.object_name), a.status);

Update Self column

Sometimes, you may need to update your own columns by using their own values, such as table test columns col1,col2 have spaces, we need trim to remove spaces, and then we can use ROWID Update, the UPDATE statement is as follows:

Update Test a  
set (col1,col2) =  
(select Trim (b.col1), Trim (b.col2) from Test b  
where  A.rowid=b.rowid)  
where exists  
(select 1 from Test b  
where  A.rowid=b.rowid)

Multiple Table Association Delete

1 use in or not to delete data

Delete from Testa where table_name

In (select object_name from TESTB);

2 delete data using exists or not exists

Delete from Testb b  
where exists   
(select 1 from Testa a where a.owner=b.owner and A.table_name=b.object_name)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/