Oracle\ms SQL Server Update Multi-Table Association update

Original: Oracle\ms SQL Server Update Multiple Table association update

A single update UPDATE statement is not able to update multiple tables unless you use triggers to suppress updates. In the update operation of the table, in many cases, you need to refer to data other than the table you want to update in an expression. Let's talk about updating the table you want to update based on other table data

first, MS SQL Server Multi-Table Association update

SQL Server provides the FROM clause of the update, which connects the table that will be updated to its data source. Although only one table can be updated, it is possible to reference data other than the table to be updated in an update expression by connecting the table that will be updated with its data source.
General form:

Update A SET field 1=b table field expression, fields 2=b table segment 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 is performed on the table to be updated, not on the new result set formed by the FROM clause

Second, Oracle Multi-Table Association update

Oracle does not have an update from syntax and can be implemented in two ways:

1, using sub-query :
Update A
SET field 1= (select field expression from B WHERE ... ）,
Field 2= (select field expression from B WHERE ... ）
WHERE logical Expression

update multiple fields two ways of writing:

Writing one:

UPDATE Table_1 A
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 A
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)

Two:

UPDATE Table_1 A
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 A
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)

Attention:

1. The value for a subquery can only be a unique value and cannot be a multivalued value.
2. Subqueries In most cases, the WHERE EXISTS clause of the last face is important, otherwise you will get the wrong result. And the WHERE EXISTS clause can be replaced by another method, as above. The final clause is a restriction on the updated record for table A, and if there is no such sentence, the updated field will be updated to NULL for a record in table A, such as a record that is not associated in table B. The WHERE EXISTS clause is to exclude the update of the record for that condition in table A.

2. Using Views:

UPDATE (SELECT a.name aname,b.name bname from A, WHERE a.id=b.id)
SET Aname=bname;

Attention:

1. Limitations for View updates:
If the view is based on connections from multiple tables, the ability to record the user update view is limited. The base table of the view cannot be updated unless update involves only one table and the View column contains the entire primary key of the table being updated.

In addition, the FROM clause of the delete in Oracle does not have the ability to join multiple tables, but only by means of 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)

Third, Oracle View Multi-table update

In Oracle, this view can be updated if the view's data source is from a single table. This view is not updatable if the view data source is from more than two tables. But sometimes we prefer to be able to update multiple table views for ease of operation.

At this point we can replace the view's original update to achieve the effect of multiple table updates by creating an update trigger.

For example:

3.1 Creating a test data table
--===================================================
--Create a test table
--===================================================
Drop Table T1;
Drop Table T2;
CREATE TABLE T1
(T11 numeric (), T12 varchar2 (20));
CREATE TABLE T2
(T11 numeric (), t22 varchar2 (20));

3.2 Multi-table View example
--===================================================
--Create Test View
--======================= ============================
Create Or Replace view T as
   select T1.t11 F1, t1.t12 F2, t2.t22 f3
& nbsp;     from T1,t2
      Where t1.t11=t2.t11;

3.3 Multi-table View trigger example       
--===================================================
--Create an alternate trigger for the 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;
This is how the definition of a multi-table updatable view works.

Be aware, however, that this trigger fails to rebuild when the view is recompiled.

Table Association UPDATE statement:

Scene:

Table:

Am_approve_list_log_sammy

Am_approve_list_sammy



Update field:

emp_id, Approver_sequence, approver_id



Related fields:

emp_id, approver_id



Method One:

UPDATE Am_approve_list_log_sammy A
SET (a.emp_id, a.approver_sequence, a.approver_id) =
(SELECT b.emp_id, B.approver_sequence, b.approver_id
From Am_approve_list_sammy b
WHERE b.emp_id = a.emp_id and b.approver_id = a.approver_id)
WHERE EXISTS (SELECT 1
From Am_approve_list_sammy C
WHERE c.emp_id = a.emp_id and c.approver_id = a.approver_id)





Method Two: (10g (including) the above version can be used)

MERGE into Gt$fm_form_field_import Imp
USING Fm_form_field fie
On (Imp.field_name = Fie.field_name
and Fie.form_kind = ' BQE. Form.7 '
and fie.table_name = ' BQEFORM7 ')
When matched then
UPDATE
SET imp.description = fie.description,
Imp.is_query = Fie.is_query,
Imp.is_default_value = Fie.is_default_value,
Imp.is_query_show = Fie.is_query_show,
Imp.is_form_show = Fie.is_form_show,
Imp.is_participant = Fie.is_participant,
Imp.is_mail_show = Fie.is_mail_show

Oracle\ms SQL Server Update Multi-Table Association update