Oracle multi-Table Association Update (update multi-Table Association)

From: http://blog.163.com/gaofx_hk/blog/static/193999289201172893813254/

Oracle does not have the update from syntax. There are two implementation methods:
1. subquery:
Update
Set field 1 = (select field expression from B where ...),
Field 2 = (select field expression from B where ...)
Where logical expression

Update multiple fields:

Statement 1:

Update table_1
Set col_x1 = (select B. col_y1, B. col_y2 from table_2 B where B. col_n = A. col_m ),
Col_x2 = (select B. col_y2 from table_2 B where B. col_n = A. col_m)
Where exists (select * From table_2 B where B. col_n = A. col_m)

Or

Update table_1
Set col_x1 = (select B. col_y1, B. col_y2 from table_2 B where B. col_n = A. col_m ),
Col_x2 = (select B. col_y2 from table_2 B where B. col_n = A. col_m)
Where a. col_m = (select B. col_n from table_2 B where B. col_n = A. col_m)

Statement 2:

Update table_1
Set (col_x1, col_x2) = (select B. col_y1, B. col_y2 from table_2 B where B. col_n = A. col_m)
Where exists (select * From table_2 B where B. col_n = A. col_m );

Or

Update table_1
Set (col_x1, col_x2) = (select B. col_y1, B. col_y2 from table_2 B where B. col_n = A. col_m)
Where a. col_m = (select B. col_n from table_2 B where B. col_n = A. col_m)

Note:

1. The subquery value can only be a unique value, not a multi-value.
2. In most cases, the where exists clause at the end is important. Otherwise, an error is returned. The where exists clause can be replaced by another method. The last clause limits the updated records of table A. If this clause is absent, for a record in Table A, if the corresponding record cannot be associated in Table B, the updated field of the record is updated to null. The where exists clause is used to exclude updates to records in this condition in table.

2. View:

Update (select a. Name aname, B. Name bname from a, B where a. ID = B. ID)
Set aname = bname;

Note:

1. Restrictions on view update:
If a view is connected to multiple tables, the user's ability to update view records is limited. The base table of the view cannot be updated unless update only involves one table and the View column contains the entire primary key of the updated table.

In addition, the from clause of delete in Oracle does not support multi-table join. It can only be done through subqueries:
Delete from Table A where exists (select * from Table B where Table A. empid = Table B. empid)
Delete from Table A where Table A. empid in (select empid from Table B)

Iii. Oracle view multi-Table update

In Oracle, a view can be updated if its data source is from a single table. If the view Data Source comes from more than two tables, the view cannot be updated. But sometimes we want to update the multi-Table view for ease of operation.

At this time, we can create an update trigger to replace the original update of this view to achieve the effect of multi-Table update.

For example:

3.1 create a test data table
-- ===================================================== ================
-- Create a test table
-- ===================================================== ================
Drop table T1;
Drop table T2;
Create Table T1
(T11 numeric (28), T12 varchar2 (20 ));
Create Table T2
(T11 numeric (28), T22 varchar2 (20 ));

3.2 examples of Multi-table views
-- ===================================================== ================
-- Create a test View
-- ===================================================== ================
Create or replace view t
Select t1.t11 F1, t1.t12 F2, t2.t22 F3
From T1, T2
Where t1.t11 = t2.t11;

3.3 Multi-Table view trigger example
-- ===================================================== ================
-- Create an alternative trigger for a view
-- ===================================================== ================
Create or replace trigger trg_insupddel_t
Instead of insert or update or delete
On T
For each row
Declare
Begin
If inserting then
Insert into T1 (T11, T12) values (: New. F1,: New. F2 );
Insert into T2 (T11, T22) values (: New. F1,: New. F3 );
Elsif updating then
Update T1 set T11 =: New. F1, T12 =: New. F2 where T11 =: New. F1;
Update T2 set T11 =: New. F1, T22 =: New. F3 where T11 =: New. F1;
Elsif deleting then
Delete from T1 where T11 =: Old. F1;
Delete from T2 where T11 =: Old. F1;
End if;
End;
In this way, you can update the definition of a view for multiple tables.

However, when the view is re-compiled, the trigger will become invalid and need to be re-built.
Comment on this
Forward to Weibo
Forward