?
Solved

Update Reference/Foreign tables

Posted on 2007-10-08
6
Medium Priority
?
234 Views
Last Modified: 2010-03-19
Database: SQL Server2005.
I have Glossary table with bunch of Foreign Key relationship tables. Glossary table has some invalid data and same exists on the other relation tables.. Now in order to delete those invalid values out of Glossary i need remove them various other tables... so i wrote a update statement for each foreign key table(reference table) but that tidous process.. is there any way i can do this.. other than updating every single foreign key table..
For instance: TableA has foreign key relations with TableB,TableC, TableD..
Fields on TableA: UniqueID,Category,Description (UniqueID is a Primary Key on Table A and Foreign key table on TableB and also Foreign key table on TableC..) So i can't delete a UniqueID on TableA since the value exists on TableB, TableC.. So i want TableB & TableC to update with correct values and delete the invalid value from TableA but i dont want to write too many update queries..
Hope my question is clear! Let me know if you have any questions.
Need to know what's the best way to deal with this issue.

Thanks,
Cindy
0
Comment
Question by:onebite2
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 20038153
If it's about 20 tables copy paste is much faster. If you think you will need id again in the future you make a stored procedure out of it.
With a select starting from sysforeignkeys you can speed up your typing.


0
 

Author Comment

by:onebite2
ID: 20040981
Is there any to get foreign key tables and the fields using sql statement, Let me know
0
 

Author Comment

by:onebite2
ID: 20041724
I had to spend more time on this but I wrote the sql.. I think its worth spending time on the sql instead of manually writing update statement. I get to know lots of new things.. informative.. but thanks for the help.


0
 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 20830406
Original question: Need to know what's the best way to deal with this issue.?

Original anwser with 3 options:
1) If it's about 20 tables copy paste is much faster.
2) If you think you will need id again in the future you make a stored procedure out of it.
3) With a select starting from sysforeignkeys you can speed up your typing.

this last option contains also the source of where to find the db-info about foreign keys.

Author's comment: ".. but thanks for the help."
My view: question answerd.  and author didn't need any additional response


-
0
 
LVL 1

Expert Comment

by:Vee_Mod
ID: 20901974
Force accepted.
Vee_Mod
Community Support Moderator
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 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