Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mckayconsulting
Starting Member
1 Post |
Posted - 2012-10-12 : 09:40:20
|
Hi all,I am working on a database at the moment and I need to change the relationship on a couple of the tables.Currently, there are three tables:[Targets] (primary key=[Tar ID])[Contracted Payments] (primary key=[CPerPID], foreign key=[Tar ID])[Actual Payments] (primary key=[AcPerPID], foreign key=[Tar ID])They are linked as follows:[Targets].[Tar ID]=[Contracted Performance Payments].[Tar ID]and[Targets].[Tar ID]=[Actual Performance Payments].[Tar ID]i.e. both the "Contracted" and "Actual" tables are related to the "Targets" table on [Tar ID].I need to change the database so that it becomes:[Targets] (primary key=[Tar ID])[Contracted Payments] (primary key=[CPerPID], foreign key=[Tar ID])[Actual Payments] (primary key=[AcPerPID], foreign key=[CPerPID])linked as follows:[Targets].[Tar ID]=[Contracted Performance Payments].[Tar ID]and[Contracted Performance Payments].[CPerPID]=[Actual Performance Payments].[CPerPID]i.e. the actual payments should link to contracted payments, not to targets.My question: since the Actual Payments table does not contain CPerPID at the moment, how would I write SQL to look at the Targets and Contracted Payments tables, figure out what the correct CPerPID is for the actual payment (based on the original [Tar ID]), and then UPDATE/SET the CPerPID to the correct value?Hopefully this makes sense, please let me know if not!Many thanks in advance for any help. |
|
Mike Jackson
Starting Member
37 Posts |
Posted - 2012-10-17 : 08:49:44
|
You probably need to post some example data so we can see the relationships.Mike |
|
|
|
|
|