Easwaran Paramasivam
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is tim_cs's =)
http://sqlfiddle.com/#!3/3 ba46/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
http://sqlfiddle.com/#!3/3
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
update e set
FreID=(select min(FreId) from tFree where Code=(select top 1 code from tFree where FreId=e.FreId))
from
tEmp e
ASKER
Excellent
Open in new window