CODEWITHSUNDEEP
mysqldatabasephpmyadmin
Stangn99
Stangn99

Reputation: 117

MySQL: Remove duplicate fields based on condition, while retaining all other data

Need some assistance with a MySQL query.

If the record has a duplicate email field, only keep the row that has STATUS = ACTIVE, while still showing all other records. 

STATUS      EMAIL
—-------------------------------
active      [email protected]
—-------------------------------
inactive    [email protected]
—-------------------------------
active      [email protected]
—-------------------------------
active      [email protected]
—-------------------------------
active      [email protected]
—-------------------------------
Inactive    [email protected]

Upvotes: 1

Views: 44

Answers (3)

slon
slon

Reputation: 1040

I have provided two queries:

  1. Select Statement. (DEMO)
  2. Delete statement that will remove two line you see in select statement

SELECT a.* FROM agents a INNER JOIN agents b ON b.EMAIL = a.EMAIL WHERE a.id <> b.id AND LCASE(a.STATUS)='inactive';

DELETE a FROM agents a 
INNER JOIN agents b ON b.EMAIL = a.EMAIL
WHERE a.id <> b.id AND LCASE(a.STATUS)='inactive';

Upvotes: 0

PatrickSJ
PatrickSJ

Reputation: 510

Assuming a few things here:

  • Rows without duplicates are not selected.
  • Duplicate rows where none are active are not selected.

DEMO

Basically a subquery determines which emails are duplicates. Outer query uses that to selected only those records which have a duplicate email.

select *
from
email_status a
inner join
(
  select email, count(email)
  from email_status
  group by email
  having count(email) > 1
) b
on a.email = b.email
where status = 'active'

Edit: Based on the response below the only criteria for selection becomes status = 'active' assuming only 1 record can be active for a given email. All that is needed is a where clause:

SELECT *
FROM email_status
WHERE status = 'active'

Upvotes: 1

Harshil Doshi
Harshil Doshi

Reputation: 3592

Following query will work:

select
(case when count(distinct status) = 2 then 'active'
      else status
 end
) as status,
email
from
yourTableName
group by email;

Upvotes: 0

Related Questions