Solved

update value in multiple tables.

Posted on 2004-09-28
10
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

752 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