SQL根据查询结果更新数据

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