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

Drop Procedure if exists LOADER_CONTACTS_ADDRESSES_UPDATE//

Create Procedure LOADER_CONTACTS_ADDRESSES_UPDATE (
    In batchnum int
)
Begin
        Update 
            CONTACTS_ADDRESSES trgt
            Inner Join LOADER_CONTACTS src
                On trgt.CONTACTS_ID_CONTACTS = src.ID_CONTACTS
            Inner Join LOADER_STATUS lst  
                On src.LOADER_STATUS_ID_LOADER_STATUS = lst.ID_LOADER_STATUS  
        Set 
            trgt.DF_COUNTRY_ID_DF_COUNTRY = Coalesce(src.DF_COUNTRY_ID_DF_COUNTRY, trgt.DF_COUNTRY_ID_DF_COUNTRY), 
            trgt.CONTACTS_ADDRESSES_LAST_UPDATE = Coalesce(src.CONTACTS_LAST_UPDATE, trgt.CONTACTS_ADDRESSES_LAST_UPDATE),
            trgt.CONTACTS_ADDRESSES_LINE_1 = Coalesce(Substring(src.CONTACTS_ADDRESSES_LINE_1, 1, 45), trgt.CONTACTS_ADDRESSES_LINE_1),
            trgt.CONTACTS_ADDRESSES_LINE_2 = Coalesce(Substring(src.CONTACTS_ADDRESSES_LINE_2, 1, 45), trgt.CONTACTS_ADDRESSES_LINE_2),
            trgt.CONTACTS_ADDRESSES_LINE_3 = Coalesce(Substring(src.CONTACTS_ADDRESSES_LINE_3, 1, 45), trgt.CONTACTS_ADDRESSES_LINE_3),
            trgt.CONTACTS_ADDRESSES_LINE_4 = Coalesce(Substring(src.CONTACTS_ADDRESSES_LINE_4, 1, 45), trgt.CONTACTS_ADDRESSES_LINE_4),
            trgt.CONTACTS_ADDRESSES_ZIP_CODE = Coalesce(Substring(src.CONTACTS_ADDRESSES_ZIP_CODE, 1, 10), trgt.CONTACTS_ADDRESSES_ZIP_CODE),
            trgt.CONTACTS_ADDRESSES_CITY = Coalesce(Substring(src.CONTACTS_ADDRESSES_CITY, 1, 45), trgt.CONTACTS_ADDRESSES_CITY),
            trgt.CONTACTS_ADDRESSES_CITY = Coalesce(src.CONTACTS_ADDRESSES_CITY, trgt.CONTACTS_ADDRESSES_CITY), 
            trgt.CONTACTS_ADDRESSES_TRASHED = Coalesce(src.CONTACTS_TRASHED, trgt.CONTACTS_ADDRESSES_TRASHED)
--             trgt.DF_CHECK_STAT_ID_DF_CHECK_STAT = Coalesce(0, trgt.DF_CHECK_STAT_ID_DF_CHECK_STAT) -- TMP_CHECK_STAT_ID_DF_CHECK_STAT
        
        Where 1 = 1  
            And lst.LOADER_STATUS_ERRCOUNT = 0  
            And lst.LOADER_STATUS_BATCH_NUM =  batchnum
			And src.ID_CONTACTS Is Not Null
        ;

End //

Delimiter ;