CODEWITHSUNDEEP
sqloracle-database
user3554452
user3554452

Reputation: 1

Deleting duplicate rows without actual match of column values

I have a table with the following columns id, name, source. Data came from multiple sources and there are duplicate records. I want to delete the duplicate records based on the name value. The name values are not identical.

Example: 

1  123David Smith Mr.   Phonebook    
2  David                addressbook    
3  John                 addressbook   
4  John abcde           Phonebook    
5  Sarah                addressbook    
6  9876Bob              Phonebook

I want a table that looks like

2 David   addressbook   123David Smith Mr.  Phonebook
4 John    addressbook   John abcde          Phonebook
5 Sarah   addressbook
6 9876Bob                                   Phonebook

Please help Thanks

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

It doesn't look like you want to delete them. It looks like you want to combine them. If I understand correctly, you want a query something like this:

select coalesce(t1.id, t2.id) as id,
       t1.name, t1.source, t2.name, t2.source
from table t1 full outer join
     table t2
     on t1.source = 'addressbook' and
        t2.source = 'phonebook'
        t2.name like '%'||t1.name||'%';

Upvotes: 1

Related Questions