一、SQL SERVER
情形1:根据关联字段更新
UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE
Table_A.col3 = 'cool';
情形2:更新特定数据
UPDATE t1
SET t1.zhibanganbubianhao = t2.zhibanganbubianhao,
t1.zhibanbanzhangbianhao = t2.zhibanbanzhangbianhao,
t1.chufangzhiriyuanbianhao = t2.chufangzhiriyuanbianhao,
t1.zhibantongxinyuanbianhao = t2.zhibantongxinyuanbianhao,
t1.zhiqingbianzubianhao = t2.zhiqingbianzubianhao,
t1.beiqingbianzubianhao = t2.beiqingbianzubianhao
FROM tb_peidang_rijihua t1,
(SELECT zhibanganbubianhao,
zhibanbanzhangbianhao,
chufangzhiriyuanbianhao,
zhibantongxinyuanbianhao,
zhiqingbianzubianhao,
beiqingbianzubianhao
FROM tb_peidang_rijihua
WHERE id = 2330) t2
WHERE t1.id = 2329;
二、MYSQL
根据一个表的查询结果插入数据:
INSERT INTO rel_user_organization (org_id, user_id, role) SELECT
org_id,
user_id,
role
FROM
(
SELECT
3 AS org_id,
id AS user_id,
1 AS role
FROM
USER
WHERE
id NOT IN (1, 4427)
) t;
根据一个表更新另一个表
UPDATE sys_biz_process_result_log AS t1,
(
SELECT
p.biz_id,
d.type_value
FROM
sys_biz_process AS p
LEFT JOIN sys_process_def AS d ON p.process_def_id = d.def_id
WHERE
d.def_type = 'ASK_FOR_LEAVE'
) AS t2
SET t1.type_value = t2.type_value
WHERE
t1.biz_id = t2.biz_id;
最新评论
接口密码是什么呀?
牛仔
一直在用的,感谢更新
哎。之前听推荐的,但是现在要付费了,否则硬解等都受到限制。