Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
*/
Drop Procedure if exists LOADER_ACCOUNTS_UPDATE//
Create Procedure LOADER_ACCOUNTS_UPDATE (
    In batchnum int
)
Begin
        Update 
            ACCOUNTS trgt 
            Inner Join LOADER_ACCOUNTS src 
                On trgt.ID_ACCOUNTS = src.ID_ACCOUNTS 
            Inner Join LOADER_STATUS lst 
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 
--             Left Join LOADER_CONTACTS TBL_3
--                 On lst.ID_LOADER_STATUS =  TBL_3.LOADER_STATUS_ID_LOADER_STATUS
--                 And TBL_3.IS_MAIN_CONTACT = 1
        Set 
            trgt.ACCOUNTS_CREATED = Coalesce(src.ACCOUNTS_CREATED, trgt.ACCOUNTS_CREATED) ,
            trgt.ACCOUNTS_LAST_EVENT_UPDATE = Coalesce(src.ACCOUNTS_LAST_EVENT_UPDATE, trgt.ACCOUNTS_LAST_EVENT_UPDATE) ,
            trgt.ACCOUNTS_REF = Coalesce(src.ACCOUNTS_REF, trgt.ACCOUNTS_REF) ,
            trgt.ACCOUNTS_TYPE = Coalesce(src.ACCOUNTS_TYPE, trgt.ACCOUNTS_TYPE) ,
--             trgt.CONTACTS_ID_CONTACTS = Coalesce(TBL_3.ID_CONTACTS,trgt.CONTACTS_ID_CONTACTS) ,
            trgt.CONTACTS_ID_CONTACTS = Coalesce(
                            (
                                Select TBL_3.ID_CONTACTS
                                From  LOADER_CONTACTS TBL_3
                                Where lst.ID_LOADER_STATUS =  TBL_3.LOADER_STATUS_ID_LOADER_STATUS
                                    And TBL_3.IS_MAIN_CONTACT = 1
                                Limit 1
                            )
                            , trgt.CONTACTS_ID_CONTACTS
                ) ,
            trgt.DF_CURRENCIES_ID_DF_CURRENCIES = Coalesce(src.DF_CURRENCIES_ID_DF_CURRENCIES, trgt.DF_CURRENCIES_ID_DF_CURRENCIES) ,
            trgt.DF_PRIORITY_ID_DF_PRIORITY = Coalesce(src.DF_PRIORITY_ID_DF_PRIORITY, trgt.DF_PRIORITY_ID_DF_PRIORITY) ,
            trgt.DF_SAND_STATE_ID_DF_SAND_STATE = Coalesce(src.DF_SAND_STATE_ID_DF_SAND_STATE, trgt.DF_SAND_STATE_ID_DF_SAND_STATE) ,
            trgt.DF_ACC_CATEG_ID_DF_ACC_CATEG = Coalesce(src.DF_ACC_CATEG_ID_DF_ACC_CATEG, trgt.DF_ACC_CATEG_ID_DF_ACC_CATEG) ,
            trgt.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES = Coalesce(src.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES, trgt.ENTITIES_AGENCIES_ID_ENTITIES_AGENCIES),
            trgt.DF_RATE_GRID_ID_DF_RATE_GRID = Coalesce(src.DF_RATE_GRID_ID_DF_RATE_GRID, trgt.DF_RATE_GRID_ID_DF_RATE_GRID),
            trgt.DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES = Coalesce(src.DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES, trgt.DF_DEBTS_TYPES_ID_DF_DEBTS_TYPES)
       Where 1 = 1 
            And lst.LOADER_STATUS_ERRCOUNT = 0 
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            -- And and Coalesce(src.ID_ACCOUNTS, 0) <> 0 -- implicite
        ;
End //

Delimiter ;





