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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenCEOCommented:
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
David L. HansenCEOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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. HansenCEOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.