Multiple Merge statement in one stored procedure

user20029524 0
Viewed 29

I get this error

Error On Execute_Stored_Procedure name System.Collections.Generic.List`1[System.String] : The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Please help me!

declare @supp table
(
    name nvarchar(max),
    accountNo nvarchar(max) 
)

set nocount off
insert into @supp(name, accountNo)  

select supp_name, supp_code 
from
    (select distinct supp_name, supp_code from @orders) s

-- CHECK FOR THE COMPANY DATA AND COMPARE IF NO FOUND THEN ADD NEW COMPANY
merge Company as C
    using (select distinct name, accountNo from @supp) as [S]
    on (convert(nvarchar, [S].accountNo)=c.accountNo)
    when matched then
        update set Name = [S].name
    when not matched then
        insert(name, accountno, companyTypeID)
        values([s].name, [s].accountno, 3);

--combine 
declare @cust table
(
    name nvarchar(max),
    accountNo nvarchar(max) 
)

insert into @cust(name, accountNo)  
select store_name, store from
(select distinct store_name, store from @orders) c

merge Company as Co
    using (select distinct name, accountNo from @cust) as [C]
    on (convert(nvarchar, [C].accountNo)=co.accountNo)
    when matched then
        update set Name = [C].name
    when not matched then
        insert(name, accountno, companyTypeID, disableConsignor, disableOutright)
        values([C].name, [C].accountno, 1, 0, 0);
merge sql
1 Answers

The problem is you are getting more than one name per code so

select supp_name, supp_code 
from
    (select distinct supp_name, supp_code from @orders) s

is returning two names for the same code.

One way to fix it is like this -- but I'm just picking a random name for the code -- you want to look at the data and see why you have more than one name and what the rule should be for picking it. Then you could change this code to reflect that rule.

select supp_name, supp_code 
from
    (select max(supp_name), supp_code 
     from @orders
     group by supp_code) s
Hogan0
Related