CODEWITHSUNDEEP
sqlsql-server
Madhukar
Madhukar

Reputation: 1242

SQL: Error when using MERGE

I'm trying to update or insert one table based on the entries present in another table. But when I try to use MERGE to accomplish this. I'm getting below error message.

Error: 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.

Do I have to include more conditions in ON clause?

Code:

 IF EXISTS (SELECT ShoppingCartNo FROM tbPOValidation WHERE ShoppingCartNo <> '')  
    BEGIN
    MERGE tbPOValidation AS t
    USING tbPOValidationTemp AS s 
    ON (CONVERT(VARCHAR(30),t.ShoppingCartNo)+CONVERT(VARCHAR(30),t.lineitemNo)+CONVERT(VARCHAR(30),t.PONo)) = 
    (CONVERT(VARCHAR(30),s.ShoppingCartNo)+CONVERT(VARCHAR(30),s.lineitemNo)+CONVERT(VARCHAR(30),s.PONo))
    --When records are matched, update 
    --the records if there is any change
    WHEN MATCHED AND (CONVERT(VARCHAR(30),t.ShoppingCartNo)+CONVERT(VARCHAR(30),t.lineitemNo)+CONVERT(VARCHAR(30),t.PONo)) = 
    (CONVERT(VARCHAR(30),s.ShoppingCartNo)+CONVERT(VARCHAR(30),s.lineitemNo)+CONVERT(VARCHAR(30),s.PONo)) 
    THEN 
    UPDATE SET t.SupplierName = s.SupplierName, t.DUNS = s.DUNS, t.PONo = s.PONo, t.LineItemNo = s.LineItemNo, t.PurchDocItemDesc = s.PurchDocItemDesc,
            t.POIssueDate = s.POIssueDate, t.DeliveryDate = s.DeliveryDate, t.PurchDocType = s.PurchDocType, t.MtrNo = s.MtrNo, t.Location = s.Location,
            t.PayTerms = s.PayTerms, t.BlanketNo = s.BlanketNo, t.BlanketLineItemNo = s.BlanketLineItemNo, t.ShoppingCartNo = s.ShoppingCartNo, 
            t.SHCItmNo = s.SHCItmNo, t.ItemPricing = s.ItemPricing, t.ItmPrcCurrency = s.ItmPrcCurrency, t.Per = s.Per, t.POValue = s.POValue, 
            t.POValueCurrency = s.POValueCurrency, t.Qty = s.Qty, t.UOM = s.UOM, t.MFGName = s.MFGName, t.MFGPartNO = s.MFGPartNO, t.Description = s.Description, 
            t.Remarks = s.Remarks, t.Accept = s.Accept, t.AcceptedBy = s.Duns, t.AcceptedOn = GETDATE(), t.RejectionReason = s.RejectionReason
    --When no records are matched, insert
    --the incoming records from s
    --table to t table
    WHEN NOT MATCHED BY TARGET THEN 
    INSERT (SupplierName, DUNS, PONo, LineItemNo, PurchDocItemDesc, POIssueDate, DeliveryDate, PurchDocType, MtrNo,
            Location, PayTerms, BlanketNo, BlanketLineItemNo, ShoppingCartNo, SHCItmNo, ItemPricing, ItmPrcCurrency, Per, POValue, POValueCurrency,
            Qty, UOM, MFGName, MFGPartNO, Description, Remarks, Accept, AcceptedBy, AcceptedOn, RejectionReason)
    VALUES (s.SupplierName, s.DUNS, s.PONo, s.LineItemNo, s.PurchDocItemDesc, s.POIssueDate, s.DeliveryDate, s.PurchDocType, s.MtrNo,
            s.Location, s.PayTerms, s.BlanketNo, s.BlanketLineItemNo, s.ShoppingCartNo, s.SHCItmNo, s.ItemPricing, s.ItmPrcCurrency, s.Per, s.POValue,
            s.POValueCurrency,s.Qty, s.UOM, s.MFGName, s.MFGPartNO, s.Description, s.Remarks, s.Accept, s.AcceptedBy, s.AcceptedOn, s.RejectionReason);
    END

Upvotes: 0

Views: 82

Answers (1)

cloudsafe
cloudsafe

Reputation: 2504

It is likely that the concatenation of fields is not unique i.e 10 11 12 would be equal to 101 11 2. Put special characters between the fields:

ON (CONVERT(VARCHAR(30),t.ShoppingCartNo)+'~' +CONVERT(VARCHAR(30),t.lineitemNo)+'~'+CONVERT(VARCHAR(30),t.PONo)) = 
    (CONVERT(VARCHAR(30),s.ShoppingCartNo)+'~' +CONVERT(VARCHAR(30),s.lineitemNo)+'~' +CONVERT(VARCHAR(30),s.PONo))

Upvotes: 2

Related Questions