0

I have a table in the format :

NAME__TELEPHONE__MONTH___YEAR

aaa______2222_________jan______2018

bbb______2222________ _____________

aaa______2222___________ ___________

Here i want to check for duplicate Telephone entries and delete all except the one which is having the month and year fields filled.

Since i am not very good in join queries any help is appreciated.. i found similar kind of question but using that i couldnt retain the one which has got other filed filled.

Thankyou in advance if anyone can help.

Community
  • 1
  • 1
AnnaMary
  • 23
  • 5

3 Answers3

1

I find out that the best answer is not delete, just move to other table, is faster and less dangerous, like this:

INSERT INTO tempTableName(id,name,telephone,month,year) SELECT DISTINCT id,name,telephone,month,year FROM tableName;

Hope it works!

José Lopez Coronado
  • 348
  • 5
  • 12
0

Why not just do this?

delete t from table t
    where month is null and year is null;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Try this : 

delete from table t
inner join 

(select name,Telephone ,row_number() over (partition by name,Telephone order by month,year ) RN
from table 
) temp 
on t.name = temp.name and 
   t.Telephone = temp.Telephone
where RN > 1
chanchal
  • 26
  • 4