一、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;