Solved

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

Posted on 2012-04-11
6
218 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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