SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 self join update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mshsilver
Posting Yak Master

110 Posts

Posted - 06/21/2010 :  10:00:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts

Posted - 06/21/2010 :  10:21:32  Show Profile  Reply with Quote


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' 




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

PBUH
Go to Top of Page

mshsilver
Posting Yak Master

110 Posts

Posted - 06/21/2010 :  11:16:27  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000