Link to home
Start Free TrialLog in
Avatar of Easwaran Paramasivam
Easwaran ParamasivamFlag for India

asked on

Identify duplicate items in one table and replace ids in another table

I've a table called tFree. Another table called tEmp. What I would like to do is that I need to identify duplicate entries in tFree table based on code. Say for ex, MED in below sample data. Then find min freid of it (3 for MED) then replace all the FreIDs (such as 10000004,  10000005 and so on) with the identified freid (3) in tEmp table. How to frame TSQL query in generic way. Please do assist.


FreId                Code
-----------------------------------------
1                  FAST
3                  MED
10000004      MED
10000005      MED
10000006      MED
10000034      FAST
10000028        Multi


FreID            EmpID
------------------------------------------------
10000015      1
10000005      2
10000007      10000004
10000012      10000005
10000011      10000006
10000009      10000007
10000004      10000008
10000010      10000009
10000006      10000010
10000033      10000016
10000022      10000023
10000024      10000028
10000032      10000029
ASKER CERTIFIED SOLUTION
Avatar of tim_cs
tim_cs
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of didnthaveaname
didnthaveaname

I think this would work: http://sqlfiddle.com/#!3/b44ad/2

/*

create table #tFree
(
	freID int not null,
	code varchar( 5 ) not null
);

create table #tEmp
(
	freID int not null,
	empID int not null
);

insert into #tFree( freID, code )
values
	( 1, 'FAST' ),
	( 3, 'MED' ),
	( 10000004, 'MED' ),
	( 10000005, 'MED' ),
	( 10000006, 'MED' ),
	( 10000034, 'FAST' ),
	( 10000028, 'Multi' );

insert into #tEmp( freID, empID )
values
	( 10000015, 1 ),
	( 10000005, 2 ),
	( 10000007, 10000004 ),
	( 10000012, 10000005 ),
	( 10000011, 10000006 ),
	( 10000009, 10000007 ),
	( 10000004, 10000008 ),
	( 10000010, 10000009 ),
	( 10000006, 10000010 ),
	( 10000033, 10000016 ),
	( 10000022, 10000023 ),
	( 10000024, 10000028 ),
	( 10000032, 10000029 );

*/

update tf1
	set tf1.freID = tf2.freID
	from
		#tFree as tf1
			inner join
				(
					select
						min(freID) as freID,
						code
					from
						#tFree
					group by
						code
				) as tf2 on tf1.code = tf2.code;

select * from #tFree

Open in new window

Here is tim_cs's =)

http://sqlfiddle.com/#!3/3ba46/2

PS - just put this to be helpful, if his answer is what you want, please give him all points.

PPS - i think his is what you want, as I misread which table you wanted to update.  not going to bother fixing mine as his works perfectly fine
Avatar of Zberteoc
update e set
	FreID=(select min(FreId) from tFree where Code=(select top 1 code from tFree where FreId=e.FreId))
from 
	tEmp e

Open in new window

Avatar of Easwaran Paramasivam

ASKER

Excellent