From statement in update

Oracle \ ms SQL Server database multi-Table Association Update and multi-Table update [original]

An update statement cannot update multiple tables unless the trigger is used for implicit update. In table update operations, data other than the table to be updated must be referenced in expressions in many cases. We will first discuss how to update the table based on the data of other tables.

I. ms SQL Server multi-Table Association update

SQL Server provides the update from clause to connect the table to be updated with other data sources. Although only one table can be updated, by connecting the table to be updated with other data sources, you can reference data other than the table to be updated in the update expression.
General format:

Update a set field 1 = B Table field expression, Field 2 = B Table field expression from B where logical expression

For example:

Update DBO. Table2
Set DBO. table2.colb = DBO. table2.colb + DBO. table1.colb
From DBO. Table2
Inner join DBO. Table1
On (DBO. table2.cola = DBO. table1.cola );

 

The actual update operation is performed on the table to be updated, rather than on the new result set formed by the from clause.

 

Ii. Oracle multi-Table Association update

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. 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.

From:
Http://hi.baidu.com/suofang/blog/item/249c067b40c8def00ad187aa.html

Oracle \ ms SQL Server database multi-Table Association Update and multi-Table update [original]

An update statement cannot update multiple tables unless the trigger is used for implicit update. In table update operations, data other than the table to be updated must be referenced in expressions in many cases. We will first discuss how to update the table based on the data of other tables.

I. ms SQL Server multi-Table Association update

SQL Server provides the update from clause to connect the table to be updated with other data sources. Although only one table can be updated, by connecting the table to be updated with other data sources, you can reference data other than the table to be updated in the update expression.
General format:

Update a set field 1 = B Table field expression, Field 2 = B Table field expression from B where logical expression

For example:

Update DBO. Table2
Set DBO. table2.colb = DBO. table2.colb + DBO. table1.colb
From DBO. Table2
Inner join DBO. Table1
On (DBO. table2.cola = DBO. table1.cola );

 

The actual update operation is performed on the table to be updated, rather than on the new result set formed by the from clause.

 

Ii. Oracle multi-Table Association update

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. 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.

From:
Http://hi.baidu.com/suofang/blog/item/249c067b40c8def00ad187aa.html