Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
1 Solution
 
tim_csCommented:
Is this what you're trying to do?


 
CREATE TABLE #Temp1 (FreID INT, Code VARCHAR(20))
CREATE TABLE #Temp2 (FreID INT, EmpID INT)
 
 
INSERT INTO #Temp1
        ( FreID, Code )
SELECT 3,'MED'
UNION ALL
SELECT 10000004,'MED'
UNION ALL
SELECT 10000005,'MED'
UNION ALL
SELECT 10000006,'MED'
UNION ALL
SELECT 10000028,'Multi'
UNION ALL
SELECT 1,'Fast'
UNION ALL
SELECT 10000034,'FAST'
 
INSERT INTO #Temp2
        ( FreID, EmpID )
SELECT 10000004,10000008
UNION ALL
SELECT 10000005,10000008
UNION ALL
SELECT 10000006,10000008
UNION ALL
SELECT 10000032,10000029
UNION ALL
SELECT 10000034,10000028
 
 
UPDATE #temp2
                SET FreID = (SELECT MIN(FreID) FROM #temp1 st1 WHERE st1.code = t1.code)
FROM
                #temp2 t2
                INNER JOIN #temp1 t1
                                ON t1.FreID = t2.FreID
 
 
SELECT * FROM #Temp1
SELECT * FROM #Temp2
 
 
DROP TABLE #Temp1
DROP TABLE #Temp2
0
 
didnthaveanameCommented:
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

0
 
didnthaveanameCommented:
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
0
 
ZberteocCommented:
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

0
 
Easwaran ParamasivamAuthor Commented:
Excellent
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now