update value in multiple tables.

Hi All,


I have a value in the primary key field of one table in SQL2000. I wanted to modify it to some other value but the problem is this field is a foriegn key to lot many other tables with huge amount of data. What is the easiest way to do this?

If this would have been Oracle, I would have disabled all constraints, modified the values in all tables and re-enabled the constraint.

What is the best way in SQL2000. Also, can i find which all table this key is a foriegn key using any tool?

Thanks in Advance,
Pankaj
LVL 1
Pankaj27Asked:
Who is Participating?
 
davehilditchCommented:
Enable cascading updates then your change will cascade through the relationships.

You can do this easily from Enterprise Manager.

Dave Hilditch.
0
 
Scott PletcherSenior DBACommented:
You could:

1) duplicate all existing rows with the new key values in the "master" table

2) change all FK table values to the new values

3) delete the original rows in the "master" table
0
 
HilaireCommented:

>>Also, can i find which all table this key is a foriegn key using any tool?<<
use
sp_fkeys <YourTable>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
For example, say the key value was changing from 1 to 5:

INSERT INTO master
SELECT 5 AS keyCol, otherCols
FROM master
WHERE keyCol = 1

UPDATE foreign1
SET keyCol = 5
WHERE keyCol = 1

UPDATE foreign2
SET keyCol = 5
WHERE keyCol = 1

...

DELETE FROM master
WHERE keyCol = 1
0
 
Pankaj27Author Commented:
Thanks Davehilditch,

I am trying to follow the approach specified by you.

I went to Enterrpise Manager->Index and keys for the parent table->Relationship tab-> and checked the Cascade Update related fields for every Relationship in the select relationship list box on that tab.

After saving, when i go for modifying the value, it gives me error with a relationship which i did not see in the above mentioned list box.  Why is that so?

Is there anyway to write a sql command to update this for every relationship possible?

Pankaj
0
 
davehilditchCommented:
When you have the Cascade for Updates option enabled, if you modify the primary key - not the foreign key - then the update should cascade.  Remember using t-sql you can only update one underlying table, so only update the primary key on one table and the foreign keys should automatically update.

For your own sanity, try it by creating a couple of test tables, one with a primary key and the other with a foreign key relationship to it (I do this via Diagrams) - enable cascading updates, and update the primary key in the first table.  You can then select from the second table to see the update.

Dave Hilditch.
0
 
Scott PletcherSenior DBACommented:
You should realize that if you change these options, they are effect for *everyone*, not just for you.  Anyone else with authority can change a primary key and it will automatically "cascade" thru the other tables; your system will not prevent pk changes the way it did before.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.