Help pelase - self join !

Question

Hi,
This is the sample table and four sample records

Create table #Employee_Cleanup
( EMp id int,
TypeOfRecord varchar(10),
name varchar(100),
address varchar(100),
email varchar(100),
phonenumber varchar(10)
)

insert into #Employee_Cleanup values (200,'new', 'WillSmith', '','','0000000')
insert into #Employee_Cleanup values (200,'old', 'WillSmith', 'philly','Hit1234@Stuff .com','43555332')

insert into #Employee_Cleanup values (100,'new', 'ChrisRock', '','','65321332')
insert into #Employee_Cleanup values (100,'old', 'ChrisRock', 'southcarolina','fun1234@Stuff .com','12321332')

Now, my requirment is to update the 'New' TypeOfRecord record with the 'old' TypeOfrecord values, so that all the blank values will be updated with values
I am finding diffuculties during self -join - I am getting duplicates. Experts, Can you lease help !

Answer 1

Hi，@KubirSingh
Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

Create table #Employee_Cleanup  
    (   
    EMp_id int,  
    TypeOfRecord varchar(10),  
    name varchar(100),  
    address varchar(100),  
    email varchar(100),  
    phonenumber varchar(10)  
    )  
      
    insert into #Employee_Cleanup values (200,'new', 'WillSmith', '','','0000000')  
    insert into #Employee_Cleanup values (200,'old', 'WillSmith', 'philly','******@gmail.com','43555332')  
      
    insert into #Employee_Cleanup values (100,'new', 'ChrisRock', '','','65321332')  
    insert into #Employee_Cleanup values (100,'old', 'ChrisRock', 'southcarolina','******@gmail.com','12321332')  
      
    select * from #Employee_Cleanup  
      
    update new  
    set new.address=old.address,new.email=old.email  
    from #Employee_Cleanup new inner join #Employee_Cleanup old  
    on old.EMp_id=new.EMp_id  
    where new.TypeOfRecord='new'and old.TypeOfRecord='old'  
      
    select * from #Employee_Cleanup  

Here is the result:

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

thanks much buddy!