mysql rs.update_mysql之数据初始化update操作

1.单表的：update user set name = (select name from user where id in (select id from user where name='小苏'))；

update goods set name = REPLACE(name ,' ','') ; //去空格

update goods set name = replace(name,'香蕉','苹果') ; //香蕉换苹果

update goods set name = price ; //将自己表中的数据赋值给另外一个属性

2..update sr_main_ww a set sys_tjzt = 4 where exists (select 1 from sr_main b where a.fk_pk_sr_main = b.pk_sr_main and b.sys_spzt = 1);

exists 用法

3..新建表需要原表的数据(sr_main ，sr_detail ，护理人数计算)

update sr_main a set a.main_xm_nam05 =

(

select count(1)

from sr_detail b

where a.pk_sr_main = b.fk_sr_main

and b.detail_bz_sad06 in ('1','2','3')

)

where a.mdjlx = 'wb_jz';

4..新建表需要原表的数据(sr_main_da ，sr_detail_da ，护理人数计算)

UPDATE sr_main_da a

SET a.main_xm_nam05 = (

SELECT

count(1)

FROM

sr_detail_da b

WHERE

a.sjbfyzj = b.fk_sr_main

AND a.sjbfnf = b.sjbfnf

AND a.sjbfyf = b.sjbfyf

AND a.sjbfnf = '2019'

AND a.sjbfyf = '11'

AND a.mdjlx = 'wb_jz'

AND detail_bz_sad06 IN ('1', '2', '3')

)

WHERE

a.mdjlx = 'wb_jz'

AND a.sjbfnf = '2019'

AND a.sjbfyf = '11'

5.right，left函数的运用

更新qlsx_bgqx,永久

-----update ycs_qlsx set qlsx_bgqx='永久';

更新qlsx_bm,需要-

-----update ycs_qlsx set qlsx_bm=(right(QLSXJBM, 9));

更新qlsx_lx

-----update ycs_qlsx set qlsx_lx=(left(QLSXJBM, 2));

-----update ycs_qlsx set qlsx_lx = CASE WHEN qlsx_lx='许可' THEN '01' WHEN qlsx_lx='处罚' THEN '03' WHEN qlsx_lx='强制' THEN '04'

WHEN qlsx_lx='征收' THEN '05' WHEN qlsx_lx='给付' THEN '06' WHEN qlsx_lx='裁决' THEN '07' WHEN qlsx_lx='确认' THEN '08'

WHEN qlsx_lx='奖励' THEN '09' WHEN qlsx_lx='权力' THEN '10' WHEN qlsx_lx='服务' THEN '14'

END ;

6.多个字段的更新

UPDATE sr_zjff_main_btff

SET zzhs = (

SELECT

count(1)

FROM

sr_main_da_btff

WHERE

fk_sr_zjff_main = ?

AND sys_spzt = 1

AND sys_scbj = 0

),

zzrs = (

SELECT

sum(mxsrs)

FROM

sr_main_da_btff

WHERE

fk_sr_zjff_main = ?

AND sys_spzt = 1

AND sys_scbj = 0

),

zzje = (

SELECT

sum(btffje)

FROM

sr_main_da_btff

WHERE

fk_sr_zjff_main = ?

AND sys_spzt = 1

AND sys_scbj = 0

)

WHERE

pk_sr_zjff_main_btff = ?