Convert a query to an update statement

I have tried a lot of option, including a simple UPDATE with subquery to convert one of my Select query to an update, but I have failed too. Instead, I am currently looping through my Select query with PHP and updating in the loop, which is ressource and time consuming. Would be great if anyone could help me to find to pack it into a single update. My query goes like :

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

i then simply do an insert for each row from the result in php using:

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

CodePudding user response：

Put your whole query in a subquery and join with it.

UPDATE psp_DC_CO_consol AS c
JOIN (
    SELECT id, PROCESS_DATE,status_in_core
    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
) AS x ON c.id = x.id
SET c.StatusTx = x.status_in_core, c.DateTx = x.PROCESS_DATE

There's no need for the subquery to select any columns other than the ones you want to use in the UPDATE.