Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

update value in multiple tables.

Posted on 2004-09-28
10
Medium Priority
?
323 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 70

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 70

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
davehilditch earned 100 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 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