Query for removing duplicates rows in temp table

Question

I have two tables, one is temptable and another one is Persons. I need below remove duplicate data from temptable.

Table one:

CREATE TABLE temptable 
  ( ID int, 
    Name varchar(255), 
    pan varchar(255), 
    Address varchar(255),
    status varchar(255) );

Table two:

CREATE TABLE Persons ( 
        ID int, 
         Name varchar(255), 
        pan varchar(255), 
          Address varchar(255),
        status varchar(255) );

The data in temptable has duplicates :

-----------------------------------------------------------
ID   Name            pan            Address        status 
-----------------------------------------------------------
1    Gopal       akkoso232l        hyd           ACCESSED
1    Gopal       akkoso232l        hyd           ACCESSED
2    sAI         aaa1213           VIZ           PENDING
3    RAM         LLWELW1213        hyd           ACCESSED
4    ONE         ONE12so232l       CHN           ACCESSED
5    REDDY       aZZoWE232l        TOW           ACCESSED
----------------------------------------------------------

6    sUNRAI      akppg8732        hyd           ACCESSED
6    sUNRAI      akppg8732        hyd           PENDING

-----------------------------------------------

I need main table data as below (no duplicates):

Persons :

------------------------------------------------
ID  Name     pan            Address  status 
------------------------------------------------
1   Gopal    akkoso232l      hyd     ACCESSED 
2   sAI      aaa1213         VIZ     PENDING
3   RAM      LLWELW1213      hyd     ACCESSED
4   ONE      ONE12so232l     CHN     ACCESSED
5   REDDY    aZZoWE232l      TOW     ACCESSED
------------------------------------------------
6   SUNRAI   akppg8732       hyd     ACCESSED
------------------------------------------------

Are you using MySQL or Oracle? Did you try searching around for a solution to this problem? — Tim Biegeleisen, Apr 11 '17 at 07:14
Oracle: http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle ... MySQL: http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table — Tim Biegeleisen, Apr 11 '17 at 07:15
If there are 2 rows for a particular `Name` in `temptable`, one with `ACCESSED` status and one with `PENDING` status, should `ACCESSED` row be present in the `Persons` table? — Nitish, Apr 11 '17 at 07:26
More generally, please define "duplicate data". If `status` can vary in `temptable` what other columns could be different? Also, why is case of `sUNRAI` corrected but not that of `sAI`??? — APC, Apr 11 '17 at 07:38
In some case i may get status only differ like ACCESSED and PENDING in this case i need to insert ACCESSED pan data only. — user187621, Apr 11 '17 at 07:48

Utsav · Answer 1 · 2017-04-11T07:39:16.607

1

Try this

insert into persons ( ID, Name, pan, Address,status)
select ID, Name, pan, Address,status 
from 
(
select t.*
,row_number over (partition bu id,name,pan,address order by status) as seq
from temptable
)
where seq=1

IF you use order by, then ACCESSED will be displayed first and then PENDING. So row_number will give seq=1 for first record of the group.

Then use this seq=1 to filter rows before inserting.

Note: If you have more status in your data other than ACCESSED and PENDING then this query might not work.

edited Apr 11 '17 at 07:39

answered Apr 11 '17 at 07:34

Utsav

7,914
2
17
38

then first one will be inserted thereby removing duplicate – Utsav Apr 11 '17 at 07:42

score 0 · Answer 2 · answered Apr 11 '17 at 07:28

0

Rather than deleting the duplicate rows from the temporary table the easiest solution is simply to filter them out when you populate the main table:

insert into Persons ( ID, Name, pan, Address,status)
select distinct ID, Name, pan, Address,status
from temptable
/

answered Apr 11 '17 at 07:28

APC

144,005
19
170
281

For the name `SUNRAI`, there are two STATUSES in `temptable`. However only one row is there in `Persons` table. – Nitish Apr 11 '17 at 07:29
Thank you for your help. The above scenario(distinct) resolve same data .but still i have one more question .if the same pan contain different status like 'ACCESSED' and PENDING in this scenario i need 'ACCESSED' pan row data only – user187621 Apr 11 '17 at 07:43
Then use [utsav's solution](http://stackoverflow.com/a/43339501/146325) – APC Apr 11 '17 at 09:24

score -1 · Answer 3 · answered Apr 11 '17 at 07:25

-1

I presume your main table is persons.

you want a query to remove duplicate value from temptable table and insert into persons. From your data it is found that id field in your table temptable is not unique as it has duplicate value of 1. If id is the key field to identify the table query should be

    insert into persons 
    select distinct(id),name,pan,address,status
    from temptable;

if you wish to distinguish pan as distinct character then use distinct funtion with pan.

answered Apr 11 '17 at 07:25

sda11

61
1
6

Thank you for your help. The above scenario(distinct) resolve but still i have one more question .But same pan contain different status like 'ACCESSED' and PENDING in this scenario i need 'ACCESSED' pan row data only . – user187621 Apr 11 '17 at 07:30

Query for removing duplicates rows in temp table

3 Answers3