CODEWITHSUNDEEP
mysqlsql
AnnaMary
AnnaMary

Reputation: 23

How can i delete duplicates rows in mysql retaining the one which is having some other fields filled?

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.

Upvotes: 0

Views: 28

Answers (3)

chanchal
chanchal

Reputation: 26

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

Upvotes: 0

José Lopez Coronado
José Lopez Coronado

Reputation: 338

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!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Why not just do this?

delete t from table t
    where month is null and year is null;

Upvotes: 0

Related Questions