1 列值的替换:
(1) 根据列值直接替换成相应标识符。
员工信息表中有xb字段,值为1和2 替换 为’男‘和’女‘
update jg_jzgjcsjzl set xb= case when xbm='1' then '男' when xbm='2' then '女' end
UPDATE Personnel SET salary = CASE WHEN salary >= 5000 THEN salary * 0.9 WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15 ELSE salary END;
(2)根据另一张表的信息替换本表中列(与另表对照)的值。
一个国标民族代码表,一张员工信息表 将员工的籍贯替换成代码(籍贯
a UPDATE test1 a
SET a.x__nativeplace =
(SELECT b.code FROM test2 b WHERE instr(a.x__nativeplace,b.info)>0)
(inter 就是b.info在a.x__nativeplace 中的位置,如果为0表示没找到)
根据两列相似的值更新另一列值)
b UPDATE overall a
SET a.x__nativeplace =
(SELECT b.info FROM gb_t2260 b WHERE b.code=a.x__nativeplace)
c update test a
set (a.prdn_st,avg_yield)=(select b.prd_st,b.avg_yield
from test11 b
where a.prd_no=b.prd_no and a.plant=b.plant )
但是需要注意的是必须要一对一,否则oracle会报错:
ORA-01427: single-row subquery returns more
than one row. Cause: You tried to execute an SQL statement that contained a subquery
that returns more than one row.
select DISTINCT 'UPDATE '|| TABLE_NAME|| ' SET ZYMC="AAAAA" WHERE ZYMC="BBBBB";' from ALL_TAB_COLUMNS where OWNER='GXJXGL' AND COLUMN_NAME='ZYMC'
----更新课程代码表 开课部门 一次性 根据一例值更新另外一列。
update kcdmb set kkbmdm = decode(kkbm,'文化传媒系',16,'经贸系',12,'财政金融系',13,'计算机信息工程系',14,'旅游系',15,16);
一次性更新一张表的多个字段
update emp
set name = 'tom' , age = 21 , sex = 'm'
------去掉列植中左边的0
update table set id=ltrim(id,'0') (trim 默认是去掉两边的空格,trim(column_name,'string' )也可以去掉其他字符
---case when 用法