Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. How do I update this command to ignore those rows that already exist 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
share|improve this question

migrated from serverfault.com Apr 18 '12 at 5:47

This question came from our site for professional system and network administrators.

1  
What SQL server are you using? Sadly, SQL isn't implemented the same on all systems. –  John Gardeniers Apr 11 '12 at 22:57
1  
Should be on STACKOVERFLOW –  Preet Sangha Apr 12 '12 at 0:17
    
Which DBMS are you using? PostgreSQL? Oracle? DB2? –  a_horse_with_no_name Apr 18 '12 at 7:14
    
What error do you get, exactly? –  ypercube Apr 18 '12 at 7:37

2 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
share|improve this answer
    
b.iProvider_PVN cannot be NULL, it's part of the Inner join condition. –  ypercube Apr 18 '12 at 7:36

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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.