SQL UPSERT HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL UPSERT HELP

Author

Topic

marksie19881
Starting Member

1 Post

Posted - 2009-11-10 : 11:26:46

Hi,

i am trying to do a sql upsert but am having some issues, i have two tables which i need to match two columns supp_id and manf_part_no here is my SQL


USE [MyDatabase]
GO
merge into products as Target
using tempproduct as Source
on Target.supp_id=Source.supp_id AND Target.manf_part_no=Source.manf_part_no
when matched then 
update set Target.avail_qty=Source.avail_qty,
Target.cost_price = Source.cost_price
when not matched then
insert (avail_qty, cost_price, manf_part_no, supp_id) values (Source.avail_qty,Source.cost_price,Source.manf_part_no, Source.supp_id);

do you know why this wont work i get the following error:

Msg 8672, Level 16, State 1, Line 1
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.

it is really bugging me now and i dont know sql well enough to fix this myself

Thanks
Steve

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-10 : 15:15:14

you've got dupes. this oughtta find 'em for ya


SELECT	Source.supp_id, Source.manf_part_no, count(Source.supp_id)
FROM	products Target
JOIN	tempproduct Source
on	Target.supp_id = Source.supp_id
AND	Target.manf_part_no = Source.manf_part_no
GROUP BY
	Source.supp_id, Source.manf_part_no
HAVING	Count(Source.supp_id) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources