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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Changing primary key

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
Go to Top of Page
   

- Advertisement -