Solved

update value in multiple tables.

Posted on 2004-09-28
10
318 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

749 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