June 7, 2011 at 3:09 pm
I have a DB1 with
table1 has no PK or FK but contains acctnumber
table2 has FK1 as acctnumber and FK2 snumber
table3 has PK snumber
table4 has PK, FK1 snumber
table5 has PKFK1 pnumber which is equal to snumber (don't ask i didn't make this piece of crap)
ok so now that we have that, i am trying to update snumber with DB2.dbo.t1.TIN when DBW@.dbo.acctnumber = DB1.dbo.table3.snumber
i got update statement conflicted with the FK
so given the DB design what do you guys think is the best way to go about updating the snumber?
is this a update all the records in one update statement? or is there a different way to go about this?
Thanks
June 7, 2011 at 3:34 pm
Update a column snumber? What table? This is totally vague but I am assuming you are trying to do something like
update table3 set snumber = [some value] where snumber = [some other number]
Just update table2 set snumber = [some value] where .... blah blah blah
If you really want some help please see the link in my signature about how to post a question in such a way as to generate the best responses. We can't see over your shoulder so what we have is 5 tables of pseudocode with no datatypes and very vague idea of what you are trying to do. So please...help us help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2011 at 4:04 pm
I would say it doesn't matter if its particularly vague, you need to ADD a record into table three for the new value THEN do your updates then delete the old record. The reason is that because those records have a relationship to the value they can't change unless it is too a new also valid value in table 3. I would absolutely do this in a transaction otherwise a problem anywhere along the line causes the database to be left in an unknown state.
Clear?
CEWII
June 7, 2011 at 4:13 pm
sorry, i work well with pseudo code. lets take data types out of the picture or assume all datatypes to be the same.
here is the overall story snumber is SSN or TIN or that is what it should be. The problem is a lot of them have letters in them.
I have built another table that contains the problematic acctnumbers, the bad SSN, and the good SSN
The problem comes from trying to update a FK or a PK i get a violation either FK or PK depending on what table i try to update.
I am simplly asking for advise when using update on the PK and FK fields. The main problem i think may be the table that has both values acctnumber and SSN does not have a PK, just a guess though.
I know this is vague, but this DB is truely a mess several tables do not have keys and data is inconsistant, so i was trying to take some of that out of the question maybe i took to much out.
June 7, 2011 at 4:24 pm
Well some what clear the only problem i see with this is the data in the PK is what needs corrected and the table that it is in does not have the acctnumber which is the only way for me to match the data with what should be the correct PK. Let me ask you this, this is for a conversion to a new product so do you think it would be easier to insert the data into a DB that i setup use the acctnumber as the PK then update all the records?
June 8, 2011 at 11:09 am
Perhaps it would help to be very specific about how foreign keys work. A value that is reference by a foreign key can usually not be changed because it will violate that key. A value that is referencing a foreign key CAN be changed but only to another valid value in the related table.
You seem to have a design that does not lend well to any changes of those keys, which is one of the reasons that I use ID values for those kind of references.
On second thought.. Script the foreign keys that are in effect, take database backup, drop them, make the changes IN A TRANSACTION, re-create the foreign keys. do this off hours.
CEWII
June 8, 2011 at 1:50 pm
i thought about that, dropping the keys, i will make sure i use a transaction the error catching looks very handy.
thanks for the tip on that.
June 8, 2011 at 2:03 pm
Since this is a one time data fix maybe you could insert a record with the bad snumber into the original table (assuming it is not the primary key). This would then let you update the original without breaking the foreign key relation. Then you can update the other tables to match the new value. Then the last step would be to remove the dummy records created. Of course if snumber is a primary key you would to remove it as the primary key first. Of course the other caveat is I am assuming you would be able to use some other column(s) to identify your placeholders so you can find them later. This would let you update everything in place with minimal impact by not forcing you to remove all the foreign keys.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2011 at 9:10 pm
yep that is the problem, snumber is the primary key. Pretty much my only option if i need to keep the DB intact is drop the keys make my chnage then add the keys back. I have a conference call with the vendors team on monday so we will see if they need the DB in the current format or if it doesn't matter as long as i give them a data map.
June 9, 2011 at 7:10 am
You might be able to just drop the primary key on that table and add the dummy records with the new value long enough to allow you to change the original primary key, remove the dummies and recreate the primary key. Not sure what other impact that may have on things though. Seems like a more direct route with a lot less overhead since you really only need to change the keys on the primary table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2011 at 3:40 pm
yeah that might work as well.
one hell of a last two days, 15 hr day yesterday and then a major fiber cut and a telco failure today good times.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply