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 = (
sum(mxsrs)
zzje = (
sum(btffje)
pk_sr_zjff_main_btff = ?