update 操作用法

--update 这个字段的所值为2
update tab a set a.字段1=2;

--带条件的update
update tab a set a.字段1=2 where id=10000;

--根据一个表update 另外一个表
update tab a set a.字段=(select b.字段 from tab1 b where a.id=b.fid)
where exists (select 1 from tab1 b where a.id=b.fid)

--update 多个字段
update tab a set (a.字段,a.字段1)=(select b.字段,b.字段1 from tab1 b where a.id=b.fid)
where exists (select 1 from tab1 b where a.id=b.fid)

update tab a set a.字段=(select b.字段 from tab1 b where a.id=b.fid) where a.字段1='地名'

 

在oracle的update语句语法中，除了可以update表之外，也可以是视图，所以有以下1个特例：

update (select a.city_name,b.city_name as new_name
from customers a,
tmp_cust_city b
where b.customer_id=a.customer_id
)
set city_name=new_name

这样能避免对B表或其索引的2次扫描，但前提是 A(customer_id) b(customer_id)必需是unique index或primary key。否则报错：

update 和 decode 结合使用
update tpr_fwzh set jjbz=decode(jjbz,1,2,2,4,3,1,4,6,5,3,6,10,7,7,8,8,10,9,jjbz)

 

update 与case when 结合使用

--修改对应关系
UPDATE TEMP_ZS_PXJH_MXFW SET XZQ= --行政区
CASE XZQ
WHEN '0,27,28,29,30,31,32,33,34,35,59' THEN
'320202,320203,320204,320205,320206,320211,320291,320281,320282,320215'
WHEN '0,27,28,29,30,31,32,33,34,59' THEN
'320202,320203,320204,320205,320206,320211,320291,320281,320215'
WHEN '0,27,28,29,30,31,32,33,34,59,35' THEN
'320202,320203,320204,320205,320206,320211,320291,320281,320282,320215'
WHEN '0,27,28,29,30,31,32,33,59' THEN '320202,320203,320204,320205,320206,320211,320291,320215'
WHEN '34' THEN '320281'
WHEN '0,35' THEN '320282'
WHEN '0,34' THEN '320281'
WHEN '0' THEN NULL
ELSE XZQ END,
ZZDJ= --资质等级
CASE ZZDJ
WHEN '0,2,3,46,47,70' THEN '2,3,1,4,5'
WHEN '0,2,3,46,47' THEN '2,3,1,4'
WHEN '0' THEN NULL
ELSE ZZDJ END,
HYLX= --会员类型
CASE HYLX
WHEN '0,4,5,48,64,71' THEN '1,2,3,4,5'
WHEN '0,4,5,48' THEN '1,2,3'
WHEN '0,4,5,48,64' THEN '1,2,3,4'
WHEN '0' THEN NULL
ELSE HYLX END

 

转载于:https://www.cnblogs.com/tianmingt/p/4447760.html