SQL 中更新一个表的数据是从另外的表(或者自己本身的表)查询出来的

模板1：

update 表1 set  表1.字段1 = 

(

select 表1字段或者表2字段

from  表2

where  表1主键 = 表2外键 

及其他条件

)  where 表1.字段 = ？

样例1：

行政区划更新：

update sjycpc_report a set qhmc_qx = (
select XALLJGMC from mz_xzjg where NODE_ID = a.xzqh_qx
)

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 detail_bz_sad06 IN ('1', '2', '3')
) ，

a.main_xm_nam06 = (
SELECT
count(fk_sr_main)
FROM
sr_detail b
WHERE
a.pk_sr_main = b.fk_sr_main
AND detail_bz_sad06 IN ('1', '2', '3')
) where a.mdjlx = 'wb_jz'

 ----------------------------------------------

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 = ?

 -------------------------------------------------------------------------

mysql更新字段为拼接字符串

UPDATE music SET url=CONCAT('/',url);

-------------------------------------------------------------------------------

初始化表和update表为同一张表，初始化失败的报错，改为子查询

update dsr_hc_jz e
set ffbmsl = 13
where pk_hc_jz in(
select pk_id from (
SELECT
pk_hc_jz as pk_id
FROM
dsr_hc_jz a,
mz_xzjg b,
dsr_hc_pc c
WHERE
a.jhdpc = c.pch
AND a.sys_xzqh = b.xzqh_id
AND b.node_id LIKE '33%'
AND a.sys_scbj = 0
AND a.sys_sjbj = 2
AND a.jzzbj = 0
AND c.pk_hc_pc = '7d91ab10923642a28a84a1ccb4ae6757'
)d
)

-----------------------------------------------------------------------------------

//根据不同的核对类别（年审、季审）更新救助复核日期

UPDATE sr_main
SET mjzjsrq = CASE
WHEN mdblx = '2' THEN
DATE_FORMAT(
date_add(curdate(), INTERVAL 3 MONTH),
'%Y-%m-%d'
)
ELSE
DATE_FORMAT(
date_add(curdate(), INTERVAL 1 YEAR),
'%Y-%m-%d'
)
END
WHERE
pk_sr_main = '1000191153666789376';

 -------------------------------------------------------------------------------