self join update - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

self join update

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-06-21 : 10:00:11

Hi, I have been trying to work out a query where I do a join on the same table. I know how to do the join but I am not sure how to get the results I’m looking for.

I have a table called contact in that table, below is a sample with data. There thousands of rows in that table and the record types are determined by the field record_type. There are only learners and employers as record types in the database.

What I need to do is and update query that takes the employer record client_id and matches it to the learner client_ids and then updates those learner client_ids with the uniqueid of that employer.

Thanks for any help:

So the results from my example below will show:

Uniqueid company firstname lastname client_id record_type
uid11111 Company a John S 111 Employer
uid22222 Company a James S 111 Learner
uid33333 Company a Bob S 111 Learner
uid44444 Company a Tim S 111 Learner

The results I need it to show are:

Uniqueid company firstname lastname client_id record_type
uid11111 Company a John S 111 Employer
uid22222 Company a James S uid11111 Learner
uid33333 Company a Bob S uid11111 Learner
uid44444 Company a Tim S uid11111 Learner


/****** Object:  Table [dbo].[contacts]   ******/
CREATE TABLE [dbo].[contacts] (
            [uniqueid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [client_id] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
            [record_type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid11111','Company a','John','S','111','Employer')

INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid22222','Company b','James','T','111','Learner')

INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid33333','Company c','Bob','K','111','Learner')

INSERT INTO contacts (uniqueid, company, firstname, lastname, client_id, record_type)VALUES ('uid44444','Company d','Tim','Y','111','Learner')

Sachin.Nand

2937 Posts

Posted - 2010-06-21 : 10:21:32

[code]

update c1 set c1.client_id= case when c1.record_type='Learner'
then c2.uniqueid else c1.client_id end from contacts c1 inner join contacts c2 on c1.client_id=c2.client_id
and c2.record_type='Employer'

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mshsilver
Posting Yak Master

112 Posts

Posted - 2010-06-21 : 11:16:27

Great, that looks like it did the trick. Thank you for your help! I'll be keeping that one in my pocket for future use. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources