一、
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);
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
;
使用快速游标的好处很多，可以支持复杂的查询语句，更新准确，无论数据多大更新效率仍然高，但执行后不返回影响行数。
三、
结论
|
方案
|
建议
|
|
标准
update
语法
|
单表更新或较简单的语句采用使用此方案更优。
|
|
inline view
更新法
|
两表关联且被更新表通过关联表主键关联的，采用此方案更优。
|
|
merge
更新法
|
两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
|
|
快速游标更新法
|
多表关联且逻辑复杂的，采用此方案更优。
|
实时测试的速度：
--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
where
a.people_number=b.people_number
and
a.year=
'2011'
and
a.city_number=
'M00000'
and
a.town_number=
'M51000'
)
--3.281
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
;
--1.641
merge
into
t_join_situation a
using
t_people_info b
on
(a.people_number=b.people_number
and
a.year=
'2011'
and
a.city_number=
'M00000'
and
a.town_number=
'M51000'
)
when
matched
then
update
set
a.join_state=b.join_state
-------------------------------------------------------------
4、新增加的DELETE子句
Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.
下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.
SQL> MERGE INTO products p
2 USING newproducts np
3 ON (p.product_id = np.product_id)
4 WHEN MATCHED THEN
5 UPDATE
6 SET p.product_name = np.product_name,
7 p.category = np.category
8 DELETE WHERE (p.category = 'ELECTRNCS')
9 WHEN NOT MATCHED THEN
10 INSERT
11 VALUES (np.product_id, np.product_name, np.category)
SQL> /
4 rows merged.
SQL> SELECT * FROM products;
PRODUCT_ID PRODUCT_NAME CATEGORY
---------- -------------------- ----------
1501 VIVITAR 35MM ELECTRNCS
1600 PLAY GYM TOYS
1601 LAMAZE TOYS
1666 HARRY POTTER TOYS
1700 WAIT INTERFACE BOOKS
SQL>
|
产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.
770
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
