Solved

update value in multiple tables.

Posted on 2004-09-28
10
316 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:Pankaj27
10 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12171074
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12171086

>>Also, can i find which all table this key is a foriegn key using any tool?<<
use
sp_fkeys <YourTable>
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 12171097
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 4

Accepted Solution

by:
davehilditch earned 25 total points
ID: 12171204
Enable cascading updates then your change will cascade through the relationships.

You can do this easily from Enterprise Manager.

Dave Hilditch.
0
 
LVL 1

Author Comment

by:Pankaj27
ID: 12171906
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
 
LVL 4

Expert Comment

by:davehilditch
ID: 12172444
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 25 total points
ID: 12172479
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

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

822 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