Can't find an exact answer among the searches I've done for my problem. I have a table that is a matrix of customer parts. I'm trying to do a bulk update from a spreadsheet that I've imported and I'm getting stopped by duplicate key values that I need to find a way around

The destination table (cpmatrix) looks similar to the below (ommitted unneeded columns)

And so on. So the part that's causing me issues is that the spreadsheet upload that I've done contains about 12000 records of customer numbers and associated parts. As is the nature of the matrix, there can be duplicates of the customer number, and duplicates of the part number which isn't my issue. My issue is that the matrix constraint is on a combination of the two columns

So - it's fine to have CustA duplicated 1000 times, and it's fine to have PartA duplicated 1000 times but I can't have two rows where CustA is the customer and PartA is the part. The combination of Customer and part is where my constraint lies and I can't figure out how to do an insert that checks for that combination and skip the line insert if the combination exists

I'm currently stuck with the basic

INSERT INTO cpmatrix 
(cpmcust, cpmpart)

SELECT
customer, part
FROM table_2

Having tried a number of different joins, NOT IN, NOT EXISTS etc

CodePudding user response：

I think you just need to combine a exists with a distinct

INSERT INTO cpmatrix 
(cpmcust, cpmpart)

SELECT
distinct customer, part
FROM table_2
WHERE NOT EXISTS (
SELECT * FROM cpmatrix 
WHERE customer = cpmcust
part = cpmpart)