Removing Duplicated Rows From a Table

To remove the repatriation and duplication of rows from a table follow the steps in the following example:

let's say that we have the following "orgTBL" table:

id name age address

1 Victor 29 LA

1 Victor 29 Dubai

2 Ali 23 London

2 Ali 23 Abu Dhabi

3 Lewis 29 Paris

1 Victor 29 Jerusalem

create a temporary table with the same structure as the orgTBL and name it tempTBL (for example):

select *

into #tempTBL

from orgTBL

where 1=0

fill the temporary table #tempTBL with a distinct selection of the key column or columns, in our example the id, name, and age, and null or empty values in the other columns, in our example the address column: 

insert into #tempTBL select distinct id,name,age,'' from orgTBL

now if you query #tempTBL you will get:

id name age address

1 Victor 29

2 Ali 23

3 Lewis 29

fill the rest of the columns (non-key column) in #tempTBL form ANY row in OrgTBL that has the same key columns values as the updated row in #tempTBL:

update #tempTBL set address =(select top 1 address from OrgTBL  where #tempTBL.id=OrgTBL.id and #tempTBL.name=OrgTBL.name and #tempTBL.age=OrgTBL.age )

empty OrgTBL:

Truncate table OrgTBL

refill OrgTBL from #tempTBL

insert into OrgTBL select * from #tempTBL

remove the temporary table:

Drop table #tempTBL

now if you query OrgTBL you will get:

id name age address

1 Victor 29 LA

2 Ali 23 London

3 Lewis 29 Paris