Oracle\MS SQL Server Update多表关联更新

本是古典何须时尚 2021-06-26 16:06 481阅读 0赞

一条`Update`更新语句是不能更新多张表的，除非使用触发器隐含更新。而表的更新操作中，在很多情况下需要在表达式中引用要更新的表以外的数据。我们先来讨论根据其他表数据更新你要更新的表

## 一、MS SQL Server 多表关联更新 ##

sql server提供了`update`的`from` 子句，可以将要更新的表与其它的数据源连接起来。虽然只能对一个表进行更新，但是通过将要更新的表与其它的数据源连接起来，就可以在`update`的表达式 中引用要更新的表以外的其它数据。

一般形式：

update A SET 字段1=B表字段表达式, 字段2=B表字段表达式
    from B WHERE 逻辑表达式

例如：

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);

实际更新的操作是在要更新的表上进行的，而不是在`from`子句所形成的新的结果集上进行的

## 二、Oracle 多表关联更新 ##

Oracle没有`update from`语法，可以通过两种实现方式：

### 1、利用子查询： ###

update A   
    SET    字段1=（select    字段表达式    from    B    WHERE    ...）,   
           字段2=（select    字段表达式    from    B    WHERE    ...）   
    WHERE    逻辑表达式

**`UPDATE`多个字段两种写法：**

写法一：

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)

或

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)

写法二：

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);

或

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)

**注意：**

1.  对于子查询的值只能是一个唯一值，不能是多值。
2.  子查询在绝大多数情况下，最后面的`where EXISTS`子句是重要的，否则将得到错误的结果。且`where EXISTS`子句可用另一方法代替，如上。最后的子句是对a表被更新记录的限制，如无此句，对于a表中某记录，如在b表中关联不到对应的记录,则该记录被更新字段将被更新为`null`。`where EXISTS`子句就是排除对a表中该情况的记录进行更新。

### 2、利用视图： ###

UPDATE (SELECT a.name ANAME,
                   b.name BNAME
            FROM   a,
                   b
            WHERE  a.id = b.id)
    SET    aname = bname;

**注意：**

1.  对于视图更新的限制：  
    如果视图基于多个表的连接，那么用户更新（`update`）视图记录的能力将受到限制。除非`update`只涉及一个表且视图列中包含了被更新的表的整个主键，否则不能更新视图的基表。

另外，Oracle中的`Delete`的`from`子句也没有多表联接的功能，只能通过子查询的方式来做：

delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
    delete from 表A where 表A.empid in (select empid from 表B)

## 三、oracle视图多表更新 ##

在oracle中通常如果视图的数据源来自单表则该视图可以进行更新。而如果视图数据源来自两个以上表时这个视图是不可更新的。但有时候为了操作的方便我们更希望能够对多表视图也进行更新。

这时候我们可以通过建立更新触发器来替代该视图原有更新以达到多表更新的效果

例如：

### 3.1 创建测试数据表 ###

--===================================================
    --创建测试表
    --===================================================
    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 多表视图范例 ###

--===================================================
    --创建测试视图
    --===================================================
    create Or Replace view t as
       select T1.t11 f1 ,T1.t12 f2 ,T2.t22 f3
          from T1,T2 
          Where T1.t11=T2.t11;

### 3.3 多表视图触发器范例 ###

--===================================================
    --创建视图的替代触发器
    --===================================================
    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;

如此即实现多表可更新视图的定义工作 。

但要注意当视图进行重新编译的时候这个触发器会失效需要重建。

表关联更新语句：

场景：

表：

AM_APPROVE_LIST_LOG_SAMMY
    
    AM_APPROVE_LIST_SAMMY

更新栏位：

EMP_ID, APPROVER_SEQUENCE, APPROVER_ID

关联栏位：

EMP_ID,   APPROVER_ID

方法一：

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)

方法二：(10g(含)以上版本才可以使用)

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