10

I have a table having person names address and job title. the names are repeating sometimes. I have to compare if two people have the same name and same address than i have to keep only 1 record of them.

Table: Data_Excel
Name: P_Name
Address: P_Address
City:  P_city
juergen d
  • 201,996
  • 37
  • 293
  • 362
Mouzzam Hussain
  • 449
  • 3
  • 8
  • 20
  • See this: http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle – Younes Nov 20 '13 at 11:08
  • Er, what about the possibility of two _different_ people with the same name and address? Like two John Smiths in a fraternity? Or a married couple with unisex names? Is that something you need to worry about? – Clockwork-Muse Nov 20 '13 at 12:17

2 Answers2

21

To find the duplicates you can do:

SELECT P_name,
  P_Address,
  P_city
FROM Data_Excel
GROUP BY P_Name,
  P_Address,
  P_city
HAVING COUNT(*) > 1;

To remove duplicates you could do:

DELETE
FROM Data_Excel
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM Data_Excel
    GROUP BY P_Name,
      P_Address,
      P_city
    );

To Insert in Person table you would do:

INSERT INTO Person(id,name)
SELECT (SELECT MAX(id)+1 FROM Person),P_Name 
FROM Data_Excel WHERE P_Name NOT IN (SELECT name FROM Person)
Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • Thanks for the prompt response, now if i have to insert the names in Person table with ID and Name from Data_excel table with checking: Name should be added once Name should not exist in person table already – Mouzzam Hussain Nov 20 '13 at 11:22
  • 1
    @user408437. I added a simplistic way of doing that. – Filipe Silva Nov 20 '13 at 11:25
  • If i dont delete the duplicate values FROM Data_Excel and than do the insert? and the ID in persons table is not auto incrementing by itself in that case how can i take the last id and increment the values of ID for each insert? – Mouzzam Hussain Nov 20 '13 at 11:34
  • I edited my answer. Any reason not to do the auto_incremented id? – Filipe Silva Nov 20 '13 at 11:36
  • Thanks, Just to be sure this query will insert names from Data_excel to Person table and if a name already exists than "WHERE P_Name NOT IN (SELECT name FROM Person)" will make sure its not already inserted? – Mouzzam Hussain Nov 20 '13 at 11:51
  • Itried the query and got the following error: *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. – Mouzzam Hussain Nov 20 '13 at 12:17
  • Can you put both table structures exactly like you have them in [sqlfiddle](http://www.sqlifddle.com) and share the link? – Filipe Silva Nov 20 '13 at 12:18
  • Pleqse find the table structues qs follows: https://www.dropbox.com/s/bz3bu89ehjpom93/Data_excel.png – Mouzzam Hussain Nov 20 '13 at 12:32
  • Copy that to sqlfiddle. It makes it easier to test the queries – Filipe Silva Nov 20 '13 at 12:34
  • The error is because if i insert more than 1 row, (SELECT MAX(id)+1 FROM Person) always gives the same value can i use sequence to do it? – Mouzzam Hussain Nov 20 '13 at 13:29
2
SELECT P_Name,P_Address,count(*)
FROM Data_Excel
GROUP BY P_Name,P_Address
HAVING count(*) > 1;

This will give you the records with same P_Name & P_Address.

SajjadHashmi
  • 3,795
  • 2
  • 19
  • 22
  • Thanks for the prompt response, now if i have to insert the names in Person table with ID and Name from Data_excel table with checking: Name should be added once Name should not exist in person table already – Mouzzam Hussain Nov 20 '13 at 11:19
  • you want to first check that names exist in the `person` table? if not than insert them and if yes than do nothing? Is that what you want? – SajjadHashmi Nov 20 '13 at 11:24
  • Yes indeed and in case i have multiple entries in Data_excel i only enter the name once. – Mouzzam Hussain Nov 20 '13 at 11:26