Solved

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

Posted on 2013-06-25
5
277 Views
Last Modified: 2013-06-25
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
Comment
Question by:Easwaran Paramasivam
5 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 39274947
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
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39274959
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
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39274974
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275191
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
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39277078
Excellent
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now