How to eliminate rows when already existing... Simple I think

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
LVL 2
prositAsked:
Who is Participating?
 
David L. HansenConnect With a Mentor Programmer AnalystCommented:
I REALLY want to help you.  It's the referential integrity preventing you.  Do you know which rows are causing the problem? Which ones that would be violated if/when you update them?
0
 
David L. HansenProgrammer AnalystCommented:
You really shouldn't be doing that.  Those restrictions are there for a reason.  Why do you want to violate the rules of your system?
0
 
prositAuthor Commented:
This is a one-time update that has to happen because of structure changes, so I'm not violating any rules...

I'm in the middle of a production move and in dire need of help with this.

Tnx
J
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
prositAuthor Commented:
Haha, it ended up being bad data, the business is going to get a smack in the head...

Enjoy the points :)

~j
0
 
David L. HansenProgrammer AnalystCommented:
Apologies, I thought you wanted to force the rows in question to break referential integrity.  Now that I reread your post, you just want to update all the rows (in your query) that DON'T conflict with those rules.  Is that right?  Again, sorry.
0
 
prositAuthor Commented:
Yeah that's right but it turned out there were multiple values in the table so with the key constraint it obviously barfed...

J
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.