Solved

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

Posted on 2013-06-25
5
279 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql user function 7 31
Minus first query 1 36
xpath sql query 2008 8 41
SQL server is using more virtual memory. 5 63
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

9 Experts available now in Live!

Get 1:1 Help Now