An update to ignore existing rows

by Funchy   Last Updated October 29, 2018 15:06 PM

I have a table with a foreign key constraint and the command below gives me an error because it's trying to set a value that already in the provider table.

UPDATE b
SET b.iProvider_PVN = a.POIN
FROM dbo.ASPVNTOPOIN_stg a
INNER JOIN dbo.Provider b ON a.ASPVN = b.iProvider_PVN
      AND b.vcProv_Type = 'IPA'
LEFT JOIN dbo.Provider c ON a.POIN = c.iProvider_PVN
WHERE c.iProvider_PVN IS NULL

How do I modify this command to ignore those rows that already exist in the provider table?



Answers 2


Does this work for you

UPDATE b
SET b.iProvider_PVN = a.POIN
FROM dbo.ASPVNTOPOIN_stg a
INNER JOIN dbo.Provider b ON a.ASPVN = b.iProvider_PVN
      AND b.vcProv_Type = 'IPA'
LEFT JOIN dbo.Provider c ON a.POIN = c.iProvider_PVN
WHERE c.iProvider_PVN IS NULL
AND b.iProvider_PVN IS NOT NULL
Preet Sangha
Preet Sangha
April 12, 2012 00:17 AM

It sounds like you're trying to perform a set based update (which is great) whilst at the same time apply row level based rules. With a set based update, your entire update must either succeed or fail, there's no middle ground.

With this in mind, one solution would be for you to include some logic to validate that the key values do not already exist, prior to performing the update.

It's difficult to formulate a query without sample schema and data but something like this should work for you:

WITH ValidUpdateSet AS
(
    SELECT 
        A.POIN
        A.ASPVN
    FROM dbo.ASPVNTOPOIN_stg A
        LEFT OUTER JOIN dbo.Provider B
            ON A.POIN = B.iProvider_PVN
    WHERE B.iProvider_PVN IS NULL
)
UPDATE b
    SET b.iProvider_PVN = a.POIN
FROM ValidUpdateSet a
    INNER JOIN dbo.Provider b 
        ON a.ASPVN = b.iProvider_PVN AND b.vcProv_Type = 'IPA'
John Sansom
John Sansom
April 18, 2012 08:01 AM

Related Questions


Updates with many rows and lock escalation

Updated November 07, 2018 16:06 PM

Scrambling PII data in CLOB columns

Updated September 11, 2018 07:06 AM


Mark exported rows as exported

Updated October 29, 2018 17:06 PM

Why is MySQL "upsert" so slow?

Updated June 23, 2018 23:06 PM