Unanswered: UPDATE problem with foreign key constraints
I have a table that I want to run an UPDATE statement to change some data in the table, but I get this error:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__Yield__Financial_Product__ProductCode'. The conflict occurred in database 'lsmdb', table 'Financial_Product', column 'ProductCode'.
The statement has been terminated.
How do I update the ProductCode column in both tables to reflect my updated data?
Thanks, Brett, but I'm a newbie, so I can't figure out how to apply the code you referred me to to my situation. I have 2 tables(Financial_Product & Yield) which already have tons of data stored in them. What I need to do is change the data in all instances of the ProductCode column where it says 'x' to 'y'. What I'm getting out of the code you referred me to is dropping then re-creating my tables, but what about the data already in them that I want left untouched?
Originally posted by Brett Kaiser
Mine is just an example that you could cut and paste in to Query Analyzer...
Maybe if you can explain the problem a little more...
Is this a one time (biggest lie in IT btw) deal?
If so, you need to add (ie INSERT) the new Parent, then UPDATE all the children and DELETE the Old parent...
Keeping in mind the biggest lie, yes it is a one time deal, where the person who input the data entered in the wrong value, but the application where the data was entered has no edit or delete functionality.
So what you're saying is to re-create the new parent with the correct data, then delete the old, update the child, then re-establish the foreign key?