Delimiter //
/**
    In batchnum Int : num du lot, si 0 ou null traitement de l'ensembles des lignes
*/

Drop Procedure if exists LOADER_CONTACTS_UPDATE//

Create Procedure LOADER_CONTACTS_UPDATE (
    In batchnum int
)
Begin
    Set @lcu_batchnum  = batchnum ;
        update 
            CONTACTS trgt 
            Inner Join LOADER_CONTACTS src 
                On trgt.ID_CONTACTS = src.ID_CONTACTS 
            Inner Join LOADER_STATUS lst 
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS 

            Left Join LOADER_ACCOUNTS TBL_2
                On lst.ID_LOADER_STATUS =  TBL_2.LOADER_STATUS_ID_LOADER_STATUS

        Set  
            trgt.ACCOUNTS_ID_ACCOUNTS = Coalesce(src.ACCOUNTS_ID_ACCOUNTS, TBL_2.ID_ACCOUNTS, trgt.ACCOUNTS_ID_ACCOUNTS) ,  
            trgt.DF_LANGUAGES_ID_DF_LANGUAGE = Coalesce(src.DF_LANGUAGES_ID_DF_LANGUAGES, trgt.DF_LANGUAGES_ID_DF_LANGUAGE) , 
            trgt.CONTACTS_NOTE = Coalesce(src.CONTACTS_NOTE, trgt.CONTACTS_NOTE) , 
            trgt.CONTACTS_REF = Coalesce(src.CONTACTS_REF, trgt.CONTACTS_REF),
            trgt.CONTACTS_LAST_UPDATE = Coalesce(src.CONTACTS_LAST_UPDATE, now()),
            trgt.CONTACTS_TRASHED = Coalesce(src.CONTACTS_TRASHED, trgt.CONTACTS_TRASHED)

        Where 1 = 1 
            And lst.LOADER_STATUS_ERRCOUNT = 0 
--             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And lst.LOADER_STATUS_BATCH_NUM =  @lcu_batchnum
--                 And Coalece(src.ID_CONTACTS, 0) <> 0 -- implicite
        ;

        -- maj des contact principaux
        update 
            LOADER_CONTACTS src 
            Inner Join LOADER_STATUS lst 
                On lst.ID_LOADER_STATUS = src.LOADER_STATUS_ID_LOADER_STATUS
            Inner Join LOADER_CONTACTS_ACCOUNTS TBL_1
                On TBL_1.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS
            Inner Join ACCOUNTS trgt
                On trgt.ID_ACCOUNTS = TBL_1.ACCOUNTS_ID_ACCOUNTS 
        Set  
            trgt.CONTACTS_ID_CONTACTS = src.ID_CONTACTS
        Where 1 = 1 
            And lst.LOADER_STATUS_ERRCOUNT = 0 
--             And lst.LOADER_STATUS_BATCH_NUM =  batchnum
            And lst.LOADER_STATUS_BATCH_NUM =  @lcu_batchnum
--             And src.IS_MAIN_CONTACT = 1 -- implicite
            And Coalesce(src.ID_CONTACTS, 0) <> 0
        ;
End //

Delimiter ;