|
mckayconsulting
Starting Member
United Kingdom
1 Posts |
 Posted - 10/12/2012 : 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.
|
|
Changing primary key
