Solved

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

Posted on 2012-04-11
6
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 
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

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.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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