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;