September 30, 2011 at 8:43 am
I have a question about merge insert/statements. Below is a simple code where I want to insert/merge some rows into a table called MKPOP from another table that I imported into the db. The target table has a constraint on the combination of (kcustnum, kmfg, kmodel, kserialno).
I get an error that sql is trying to update the same row multiple times I believe because the input file has some dupes.
My real question is whether there is a way to tell sql to ignore the dupes and continue, or output them to another table so I can see them(sometimes I may not care). Or am I stuck with joining the tables, exporting to a 3rd table with a NOT EXISTS/NOT IN and insert/merge that table? Is there is another way?
This is typically a one time operation, not something that is sheduled or repeated.
--Update Existing Rows and Add Missing Rows
MERGE INTO MKPOP as M --Target
using NewMachines as N --Source
on M.kcustnum = N.customer
and M.kmfg = N.make
and M.kmodel = N.model
and M.kserialno = N.serial
when matched then --On match update
update set M.kmfg = N.make
when not matched then --Add Missing
insert (kcustnum, kmfg, kmodel, kserialno)
values (N.customer, N.make, N.model, N.serial)
September 30, 2011 at 9:29 am
If it's actually duplicates in the source table, change that to a subquery (inline or CTE) and use Distinct in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply