update语句的语法与原理

快来打我* 2021-09-10 13:16 683阅读 0赞

一、          update 语句的语法与原理

1.      语法

单表： UPDATE  表名称  SET  列名称  =  新值  WHERE  列名称  =  某值

如： update  t\_join\_situation  set  join\_state= '1' where year = '2011'

更新年度为“ 2011 ”的数据的 join\_state 字段为“ 1 ”。如果更新的字段加了索引，更新时会重建索引，更新效率会慢。

多表关联，并把一个表的字段值更新到另一个表中的字段去：

update  表 a set a. 字段 1 = (select b. 字段 1 from  表 b where a. 字段 2=b. 字段 2) where exists(select 1 from  表 b where a. 字段 2=b. 字段 2)

oracle 的更新语句不通 MSSQL 那么简单易写，就算写出来了，但执行时可能会报

这是由于 set 哪里的子查询查出了多行数据值， oracle 规定一对一更新数据，所以提示出错。要解决这样必须保证查出来的值一一对应。

2.      原理

Update 语句的原理是先根据 where 条件查到数据后，如果 set 中有子查询，则执行子查询把值查出来赋给更新的字段，执行更新。

如： update  表 a set a. 字段 1 = (select b. 字段 1 from  表 b where a. 字段 2=b. 字段 2) where exists(select 1 from  表 b where a. 字段 2=b. 字段 2) 。查表 a 的所有数据，循环每条数据，验证该条数据是否符合 exists(select 1 from  表 b where a. 字段 2=b. 字段 2) 条件，如果是则执行 (select b. 字段 1 from  表 b where a. 字段 2=b. 字段 2) 查询，查到对应的值更新 a. 字段 1 中。关联表更新时一定要有 exists(select 1 from  表 b where a. 字段 2=b. 字段 2) 这样的条件，否则将表 a 的其他数据的字段 1 更新为 null 值。

二、          提高 oracle 更新效率的各种解决方案

1.      标准 update 语法

当你需要更新的表是单个或者被更新的字段不需要关联其他表带过来，则最后选择标准的 update 语句，速度最快，稳定性最好，并返回影响条数。如果 where 条件中的字段加上索引，那么更新效率就更高。但对需要关联表更新字段时， update 的效率就非常差。

2.      inline view 更新法

inline view 更新法就是更新一个临时建立的视图。如： update  ( select  a.join\_state  as join\_state\_a,b.join\_state  as  join\_state\_b

from  t\_join\_situation a, t\_people\_info b  where  a.people\_number=b.people\_number

and  a.year= '2011' and  a.city\_number= 'M00000' and  a.town\_number= 'M51000' )  set join\_state\_a=join\_state\_b

括号里通过关联两表建立一个视图， set 中设置好更新的字段。这个解决方法比写法较直观且执行速度快。但表 B 的主键一定要在 where 条件中，并且是以“ = ”来关联被更新表，否则报一下错误：

3. merge 更新法

merge 是 oracle 特有的语句，语法如下：

MERGE  INTO  table\_name alias1   
 USING ( table | view | sub\_query) alias2  
 ON  ( join  condition)   
 WHEN  MATCHED  THEN    
      UPDATE  table\_name   
      SET  col1  =  col\_val1,   
         col2      =  col2\_val   
 WHEN  NOT  MATCHED  THEN    
      INSERT  (column\_list)  VALUES  (column\_values);

它的原理是 在 alias2 中 Select 出来的数据， 每一条 都跟 alias1 进行  ON (join condition) 的比较，如果匹配，就进行更新的操作 (Update), 如果不匹配，就进行插入操作 (Insert) 。执行 merge 不会返回影响的行数。 Merge 语句的写法比较繁琐，并且最多只能两个表关联，复杂的语句用 merge 更新法将力不从心且效率差。

4. 快速游标更新法

语法如：

begin

for  cr  in  ( 查询语句 )  loop  –- 循环

-- 更新语句（根据查询出来的结果集合）

end loop ;  -- 结束循环

end ;

oracle 支持快速游标，不需要定义直接把游标写到 for 循环中，这样就方便了我们批量更新数据。再加上 oracle 的 rowid 物理字段（ oracle 默认给每个表都有 rowid 这个字段，并且是唯一索引），可以快速定位到要更新的记录上。

例子如下：

begin

for  cr  in  ( select  a.rowid,b.join\_state  from  t\_join\_situation a,t\_people\_info b

where  a.people\_number=b.people\_number

and  a.year= '2011' and  a.city\_number= 'M00000' and  a.town\_number= 'M51000' )  loop

update  t\_join\_situation  set  join\_state=cr.join\_state  where

rowid  = cr.rowid;

end loop ;

end ;

使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。

三、 结论

<table style="padding:0px; margin:auto auto auto 22.5pt; font-size:12px; border-collapse:collapse; color:rgb(85,85,85); font-family:宋体,'Arial Narrow',arial,serif; widows:auto; border:medium none; width:408.1pt"> 
 <tbody style="padding:0px; margin:0px"> 
  <tr style="padding:0px; margin:0px; height:20.75pt"> 
   <td style="padding:0cm 5.4pt; margin:0px; border:1pt solid black; width:111.75pt; height:20.75pt; background-color:gray"> 
    <div style="padding:0px; margin:0px; text-align:center"> 
     <span style="padding:0px; margin:0px"><span style="padding:0px; margin:0px; font-family:宋体; color:white">方案</span></span> 
    </div> </td> 
   <td style="padding:0cm 5.4pt; margin:0px; border-top-width:1pt; border-right-width:1pt; border-bottom-width:1pt; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-color:black black black rgb(240,240,240); width:296.35pt; height:20.75pt; background-color:gray"> 
    <div style="padding:0px; margin:0px; text-align:center"> 
     <span style="padding:0px; margin:0px"><span style="padding:0px; margin:0px; font-family:宋体; color:white">建议</span></span> 
    </div> </td> 
  </tr> 
  <tr style="padding:0px; margin:0px; height:21.75pt"> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-color:rgb(240,240,240) black black; width:111.75pt; height:21.75pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">标准</span> 
     <span style="font-family:Calibri; padding:0px; margin:0px">update</span> 
     <span style="padding:0px; margin:0px; font-family:宋体">语法</span> 
    </div> </td> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-right-style:solid; border-bottom-style:solid; border-color:rgb(240,240,240) black black rgb(240,240,240); width:296.35pt; height:21.75pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">单表更新或较简单的语句采用使用此方案更优。</span> 
    </div> </td> 
  </tr> 
  <tr style="padding:0px; margin:0px; height:42.5pt"> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-color:rgb(240,240,240) black black; width:111.75pt; height:42.5pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="font-family:Calibri; padding:0px; margin:0px">inline view</span> 
     <span style="padding:0px; margin:0px; font-family:宋体">更新法</span> 
    </div> </td> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-right-style:solid; border-bottom-style:solid; border-color:rgb(240,240,240) black black rgb(240,240,240); width:296.35pt; height:42.5pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">两表关联且被更新表通过关联表主键关联的，采用此方案更优。</span> 
    </div> </td> 
  </tr> 
  <tr style="padding:0px; margin:0px; height:43.55pt"> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-color:rgb(240,240,240) black black; width:111.75pt; height:43.55pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:'Courier New'">merge</span> 
     <span style="padding:0px; margin:0px; font-family:宋体">更新法</span> 
    </div> </td> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-right-style:solid; border-bottom-style:solid; border-color:rgb(240,240,240) black black rgb(240,240,240); width:296.35pt; height:43.55pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。</span> 
    </div> </td> 
  </tr> 
  <tr style="padding:0px; margin:0px; height:21.75pt"> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-left-width:1pt; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-color:rgb(240,240,240) black black; width:111.75pt; height:21.75pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">快速游标更新法</span> 
    </div> </td> 
   <td style="padding:0cm 5.4pt; margin:0px; border-right-width:1pt; border-bottom-width:1pt; border-right-style:solid; border-bottom-style:solid; border-color:rgb(240,240,240) black black rgb(240,240,240); width:296.35pt; height:21.75pt; background-color:transparent"> 
    <div style="padding:0px; margin:0px"> 
     <span style="padding:0px; margin:0px; font-family:宋体">多表关联且逻辑复杂的，采用此方案更优。</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

实时测试的速度：

\--48466条数据

\--1.297

update  ( select  a.join\_state  as  join\_state\_a,b.join\_state  as  join\_state\_b

from  t\_join\_situation a, t\_people\_in fo b  where  a.people\_number=b.people\_number

and  a.year= '2011' and  a.city\_number= 'M00000' and  a.town\_number= 'M51000'

)  set  join\_state\_a=join\_state\_b

\--7.156

update  t\_join\_situation a  set  a.join\_state=( select  b.join\_state  from  t\_people\_info b

where  a.people\_number=b.people\_number

and  a.year= '2011' and  a.city\_number= 'M00000' and  a.town\_number= 'M51000' )

where exists  ( select 1 from  t\_people\_info b