USP_MERGETWOCONSTITUENTS_SOCIALMEDIAACCOUNT

            create procedure dbo.USP_MERGETWOCONSTITUENTS_SOCIALMEDIAACCOUNT
            (
                @SOURCEID uniqueidentifier,
                @TARGETID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as
            begin
                set nocount on;

                declare @CURRENTDATE datetime = getdate();

                -- If Source is Rejected/Confirmed, but Target is neither Rejected/Confirmed, update the Target record values with Source record values.

                update TARGETSOCIALMEDIAACCOUNT
                set
                    [SOCIALMEDIASERVICEID] = SOURCESOCIALMEDIAACCOUNT.SOCIALMEDIASERVICEID
                    ,[USERID] = SOURCESOCIALMEDIAACCOUNT.USERID
                    ,[URL] = SOURCESOCIALMEDIAACCOUNT.URL
                    ,[SOCIALMEDIAACCOUNTTYPECODEID] = SOURCESOCIALMEDIAACCOUNT.SOCIALMEDIAACCOUNTTYPECODEID
                    ,[INFOSOURCECODEID] = SOURCESOCIALMEDIAACCOUNT.INFOSOURCECODEID
                    ,[DONOTCONTACT] = SOURCESOCIALMEDIAACCOUNT.DONOTCONTACT
                    ,[CHANGEDBYID] = @CHANGEAGENTID
                    ,[DATECHANGED] = @CURRENTDATE
                    ,[SEQUENCE] = SOURCESOCIALMEDIAACCOUNT.SEQUENCE
                    ,[CONFIRMED] = SOURCESOCIALMEDIAACCOUNT.CONFIRMED
                    ,[DATECONFIRMED] = SOURCESOCIALMEDIAACCOUNT.DATECONFIRMED
                    ,[CONFIRMEDBYAPPUSERID] = SOURCESOCIALMEDIAACCOUNT.CONFIRMEDBYAPPUSERID
                    ,[REJECTED] = SOURCESOCIALMEDIAACCOUNT.REJECTED
                    ,[DATEREJECTED] = SOURCESOCIALMEDIAACCOUNT.DATEREJECTED
                    ,[REJECTEDBYAPPUSERID] = SOURCESOCIALMEDIAACCOUNT.REJECTEDBYAPPUSERID
                    ,[WPMATCHCODE] = SOURCESOCIALMEDIAACCOUNT.WPMATCHCODE
                    ,[EMAILADDRESS] = SOURCESOCIALMEDIAACCOUNT.EMAILADDRESS
                    ,[WEALTHSOURCE] = SOURCESOCIALMEDIAACCOUNT.WEALTHSOURCE
                    ,[FULLHASH] = SOURCESOCIALMEDIAACCOUNT.FULLHASH
                    ,[PARTIALHASH] = SOURCESOCIALMEDIAACCOUNT.PARTIALHASH
                from dbo.SOCIALMEDIAACCOUNT TARGETSOCIALMEDIAACCOUNT
                inner join dbo.SOCIALMEDIAACCOUNT SOURCESOCIALMEDIAACCOUNT ON SOURCESOCIALMEDIAACCOUNT.PARTIALHASH = TARGETSOCIALMEDIAACCOUNT.PARTIALHASH
                where SOURCESOCIALMEDIAACCOUNT.CONSTITUENTID = @SOURCEID
                    and TARGETSOCIALMEDIAACCOUNT.CONSTITUENTID = @TARGETID
                    and not (TARGETSOCIALMEDIAACCOUNT.CONFIRMED = 1 or TARGETSOCIALMEDIAACCOUNT.REJECTED = 1)
                    and (SOURCESOCIALMEDIAACCOUNT.CONFIRMED = 1 or SOURCESOCIALMEDIAACCOUNT.REJECTED = 1)
                    and (SOURCESOCIALMEDIAACCOUNT.PARTIALHASH is not null and ltrim(rtrim(SOURCESOCIALMEDIAACCOUNT.PARTIALHASH)) <> '')
                    and (TARGETSOCIALMEDIAACCOUNT.PARTIALHASH is not null and ltrim(rtrim(TARGETSOCIALMEDIAACCOUNT.PARTIALHASH)) <> '')

                -- If unique record in Source, move it to Target.

                update SOURCESOCIALMEDIAACCOUNT
                set
                    [CONSTITUENTID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                from dbo.SOCIALMEDIAACCOUNT SOURCESOCIALMEDIAACCOUNT
                left join dbo.SOCIALMEDIAACCOUNT TARGETSOCIALMEDIAACCOUNT ON TARGETSOCIALMEDIAACCOUNT.PARTIALHASH = SOURCESOCIALMEDIAACCOUNT.PARTIALHASH and TARGETSOCIALMEDIAACCOUNT.CONSTITUENTID = @TARGETID
                where SOURCESOCIALMEDIAACCOUNT.CONSTITUENTID = @SOURCEID
                    and (SOURCESOCIALMEDIAACCOUNT.PARTIALHASH is not null and ltrim(rtrim(SOURCESOCIALMEDIAACCOUNT.PARTIALHASH)) <> '')
                    and TARGETSOCIALMEDIAACCOUNT.ID is null

                -- If unique record in Source which is entered manually, move it to Target.

                update dbo.SOCIALMEDIAACCOUNT
                set
                    [CONSTITUENTID] = @TARGETID,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where CONSTITUENTID = @SOURCEID
                    and (PARTIALHASH is null or ltrim(rtrim(PARTIALHASH)) = '')
            end