I have wrote a merge statement to update/Insert. its working well when i remove a column from the query.
MERGE TBLCONTRACTORS AS TARGET USING [ips].dbo.TBLCONTRACTORS AS SOURCE ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR) WHEN MATCHED AND TARGET.PREFIX <> SOURCE.PREFIX THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX WHEN NOT MATCHED BY TARGET THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);
Above code is working when i change value in source table. but below code gives 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.
TARGET.ABBREVIATION <> SOURCE.ABBREVIATION is the line where i getting error. This is i know, becuase of same value is on multiple rows for merging process. Please help me on this. How i can avoid this error by adding the line mentioned in red above.
MERGE TBLCONTRACTORS AS TARGET USING [ips].dbo.TBLCONTRACTORS AS SOURCE ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR) WHEN MATCHED AND TARGET.PREFIX <> SOURCE.PREFIX OR TARGET.ABBREVIATION <> SOURCE.ABBREVIATION THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX , TARGET.ABBREVIATION= SOURCE.ABBREVIATION WHEN NOT MATCHED BY TARGET THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);