使用单个SQL查询[duplicate]在中执行批更新

UPDATE TABLE2
       JOIN TABLE1
       ON TABLE2.SERIAL_ID = TABLE1.SUBST_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

如果你需要的话,除了汤姆的回答 经常重复操作 我想 你可以做:

UPDATE TABLE1
       JOIN TABLE2
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID
WHERE TABLE2.BRANCH_ID IS NULL

UPDATE TABLE2
       JOIN TABLE1
       ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET    TABLE2.BRANCH_ID = TABLE1.CREATED_ID 
WHERE TABLE2.BRANCH_ID IS NULL or TABLE2.BRANCH_ID='';

我觉得这应该管用

UPDATE secondTable
JOIN firsTable ON secondTable.SERIAL_ID = firsTable.SUBST_ID
SET BRANCH_ID = CREATED_ID

使用 INNER JOIN :

UPDATE TABLE1
INNER JOIN TABLE2 ON TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

另一种替代方案如下:这里我使用 WHERE 从句代替 JOIN

UPDATE 
    TABLE1,
    TABLE2
WHERE
    TABLE1.SUBST_ID = TABLE2.SERIAL_ID
SET 
    TABLE2.BRANCH_ID = TABLE1.CREATED_ID;

你也可以用这个:

update TABLE1 set BRANCH_ID = ( select BRANCH_ID from TABLE2 where TABLE1.SUBST_ID = TABLE2.SERIAL_ID)

但以我的经验,我可以说,这种方式是如此缓慢,不建议它!

在SQL中使用内部连接查询进行更新非常简单 没有 FROM 条款。举个例子:

    UPDATE customer_table c 

      INNER JOIN  
          employee_table e
          ON (c.city_id = e.city_id)  

    SET c.active = "Yes"

    WHERE c.city = "New york";