2023年8月3日发(作者：)

plsql使⽤技巧及语句集锦（⼆）经典update1 列值的替换：（1） 根据列值直接替换成相应标识符。员⼯信息表中有xb字段，值为1和2 替换 为’男‘和’⼥‘update jg_jzgjcsjzl set xb= case when xbm='1' then '男' when xbm='2' then '⼥' endUPDATE 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 FROM test2 b WHERE instr(a.x__nativeplace,)>0) (inter 就是在a.x__nativeplace 中的位置，如果为0表⽰没找到) 根据两列相似的值更新另⼀列值）b UPDATE overall a SET a.x__nativeplace = (SELECT FROM gb_t2260 b WHERE =a.x__nativeplace)c update test a

set (_st,avg_yield)=(select _st,_yield

from test11 b

where _no=_no and = )

但是需要注意的是必须要⼀对⼀，否则oracle会报错：

ORA-01427: single-row subquery returns more

than one row. Cause: You tried to execute an SQL statement that contained a subquerythat returns more than one DISTINCT 'UPDATE '|| TABLE_NAME|| ' SET ZYMC="AAAAA" WHERE ZYMC="BBBBB";' from ALL_TAB_COLUMNSwhere 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'------去掉列植中左边的0update table set id=ltrim(id,'0') (trim 默认是去掉两边的空格，trim（column_name,'string' ）也可以去掉其他字符---case when ⽤法

2023年8月3日发(作者：)

plsql使⽤技巧及语句集锦（⼆）经典update1 列值的替换：（1） 根据列值直接替换成相应标识符。员⼯信息表中有xb字段，值为1和2 替换 为’男‘和’⼥‘update jg_jzgjcsjzl set xb= case when xbm='1' then '男' when xbm='2' then '⼥' endUPDATE 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 FROM test2 b WHERE instr(a.x__nativeplace,)>0) (inter 就是在a.x__nativeplace 中的位置，如果为0表⽰没找到) 根据两列相似的值更新另⼀列值）b UPDATE overall a SET a.x__nativeplace = (SELECT FROM gb_t2260 b WHERE =a.x__nativeplace)c update test a

set (_st,avg_yield)=(select _st,_yield

from test11 b

where _no=_no and = )

但是需要注意的是必须要⼀对⼀，否则oracle会报错：

ORA-01427: single-row subquery returns more

than one row. Cause: You tried to execute an SQL statement that contained a subquerythat returns more than one DISTINCT 'UPDATE '|| TABLE_NAME|| ' SET ZYMC="AAAAA" WHERE ZYMC="BBBBB";' from ALL_TAB_COLUMNSwhere 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'------去掉列植中左边的0update table set id=ltrim(id,'0') (trim 默认是去掉两边的空格，trim（column_name,'string' ）也可以去掉其他字符---case when ⽤法