求助：将MySQL查询转换为单条UPDATE语句

我尝试过多种方法，包括使用带子查询的简单UPDATE语句，将SELECT查询转换为更新操作，但均失败。目前只能通过PHP循环遍历SELECT查询结果，逐行执行更新，这种方式既耗费资源又耗时，希望能将其整合为单条UPDATE语句。

原SELECT查询语句 #

SELECT id, PAYMENT_ID, TX, DateTx, PROCESS_DATE, status_in_Psp, status_in_core,
    case when status_in_Psp=status_in_core 
        then 1 else 0 end as Alliquals
from
(
    SELECT psp_DC_CO_consol.id,psp_DC_CO_consol.PAYMENT_ID, 
        psp_DC_CO_consol.TX, date_format(BankingActivity.PROCESS_DATE,'%Y-%m-%d') as PROCESS_DATE,
        BankingActivity.PROCESS_DATE as Date_CORE,
        date_format(psp_DC_CO_consol.DateTx,'%Y-%m-%d') as DateTx,psp_DC_CO_consol.DateTx as Date_PSP,
        psp_DC_CO_consol.StatusTX as status_in_Psp,
        case when BankingActivity.`STATUS`='COMPLETED' then 1
             WHEN BankingActivity.`STATUS`='FAILED' then 0
             WHEN BankingActivity.`STATUS`='CANCELLED' then 0
             else 3 end as status_in_core
    from psp_DC_CO_consol 
    left join BankingActivity  on BankingActivity.PAYMENT_ID=psp_DC_CO_consol.PAYMENT_ID
    where SOURCE='core' and 
        DateTX > (curdate() - interval 5 day) 
) all_rec 
where
    (case when status_in_Psp=status_in_core 
          then 1 else 0 end = 0) or DateTX<>PROCESS_DATE

当前PHP逐行更新代码 #

$sql = "UPDATE psp_DC_CO_consol 
     SET StatusTx=".$row['status_in_core'].",DateTx='".$row['PROCESS_DATE']."'   WHERE id=$id;";

单条UPDATE解决方案 #

利用MySQL的UPDATE ... JOIN语法，直接关联数据来源完成批量更新：

UPDATE psp_DC_CO_consol t
JOIN (
    SELECT 
        psp_DC_CO_consol.id,
        case when BankingActivity.`STATUS`='COMPLETED' then 1
             WHEN BankingActivity.`STATUS`='FAILED' then 0
             WHEN BankingActivity.`STATUS`='CANCELLED' then 0
             else 3 end as status_in_core,
        date_format(BankingActivity.PROCESS_DATE,'%Y-%m-%d') as PROCESS_DATE
    from psp_DC_CO_consol 
    left join BankingActivity  on BankingActivity.PAYMENT_ID=psp_DC_CO_consol.PAYMENT_ID
    where SOURCE='core' and 
        DateTX > (curdate() - interval 5 day) 
) update_data ON t.id = update_data.id
SET 
    t.StatusTx = update_data.status_in_core,
    t.DateTx = update_data.PROCESS_DATE
WHERE 
    (t.StatusTX <> update_data.status_in_core) 
    OR date_format(t.DateTx,'%Y-%m-%d') <> update_data.PROCESS_DATE;

说明 #

1. 子查询update_data保留了更新所需的核心字段（id、status_in_core、PROCESS_DATE）及原过滤条件
2. 通过JOIN关联主表与子查询结果，精准匹配需要更新的行
3. WHERE条件与原SELECT过滤逻辑一致，仅更新状态不匹配或日期不一致的记录
4. 直接在数据库层面完成批量操作，避免PHP循环的额外资源消耗

内容的提问来源于stack exchange，提问作者R_life_R