Solved

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

Posted on 2012-04-11
6
215 Views
Last Modified: 2012-04-11
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
0
Comment
Question by:prosit
  • 3
  • 3
6 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37835195
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
 
LVL 2

Author Comment

by:prosit
ID: 37835203
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
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 37835308
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Closing Comment

by:prosit
ID: 37835339
Haha, it ended up being bad data, the business is going to get a smack in the head...

Enjoy the points :)

~j
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 37835353
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
 
LVL 2

Author Comment

by:prosit
ID: 37835612
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question