CODEWITHSUNDEEP
mysql
jvan
jvan

Reputation: 23

Remove duplicates from a table based on a non-duplicated field

I need to remove some duplicate data based on a non-duplicated field.

Sample Data:

|status|Size|id#   |income|scan_date |
|  0   | 3  |123456| 1000 |2015-10-16|
|  1   | 3  |123456| 1000 |2015-10-16|
|  1   | 4  |112345|  900 |2015-09-05|
|  0   | 7  |122345|  700 |2015-10-01|

When the id# and scan_date are the same, I need to remove only the rows where the status is "0".

Upvotes: 2

Views: 40

Answers (1)

Shadow
Shadow

Reputation: 34232

delete from table
where status=0
and (id, scan_date) in
(select id, scan_date from
(select id, scan_date from table
group by id, scan_date
having count(*) >=2) t1)

You need the extra subquery in mysql, since mysql does not allow to select from the table being updated or deleted from.

Update: see this sqlfiddle I created. I think you removed the subquery with the t1 alias, even though I explicitly warned you that it is important!

Upvotes: 2

Related Questions