[RESOLVED] finding dupes across 2 columns trouble with aggregates

jrough

I am having trouble with this SQL placing the aggregates.
I want to remove the dupes where there are more than one ndc across the zones in the b_unit. The group by is correct. What is happening is I am getting 1's and 2's etc in the count() column but I only need the rows that are greater than 1 . How to separate them from each other and get only the rows having >1.

select b_unit,zone,zonenm, count(*)
from ci2
group by b_unit,ndc
having COUNT(concat(bu,ndc) > 1)

many thanks,

bradgrafelman

Look at the () grouping in your COUNT statement: COUNT(concat(bu,ndc) > 1)

The '> 1' comparison is part of the parameter for COUNT(), whereas I suspect you meant to compare the value of COUNT() instead.

jrough

You are so wonderbar!!@#$%^&*()

NOw for the axe to fall, how to do the delete?

DELETE FROM ci2
GROUP BY b_unit,ndc
HAVING COUNT(concat(b_unit,ndc) )>1

Why does this not work and how should I do it?
tnx,

bradgrafelman

Does each row have a unique identifying value (e.g. an AUTO_INCREMENT column) ? If so, try doing a subquery, e.g.:

DELETE FROM ci2 WHERE id IN (<your SELECT stmnt from above>)

Res

I don't think you can run a delete statement where the sub query is acting on the same table you are deleting from.

You will want to put your data in a temporary table in memory, or create a table and copy the data into it, then run the statement as bradgrafelman posted, remembering to edit you select query to return only the id field.

DELETE FROM ci2
WHERE id IN (Select id FROM ci2_copy
GROUP BY b_unit,ndc
HAVING COUNT(concat(b_unit,ndc) )>1)

jrough

Thanks, I think it is close.
DELETE FROM ci2
WHERE id IN (select id,b_unit,zonenm,zoneid,ndc, count(*)
FROM ci2
GROUP BY b_unit,ndc
HAVING COUNT(concat(b_unit,ndc) )>1)

but I get the error message "Operand should contain 1 column(s)" ?
I do have a unique id field called id.
thanks again,

Res

That's what I mean by make sure sub-query has only the id field being returned. If you modify it so you are only returning the ID, the 'id IN' portion will be able to properly match the records.

DELETE FROM ci2
WHERE id IN (select id
FROM ci2
GROUP BY b_unit,ndc
HAVING COUNT(concat(b_unit,ndc) )>1)

I'm almost certain this will still fail because you cannot run a subquery against a table that you are deleting from in the parent query.

Something like this should work.

$sql1 = "CREATE TEMPORARY TABLE tmp_ci2 SELECT * FROM";
//run query 1
$sql2 = "DELETE FROM ci2
WHERE id IN (select id
FROM tmp_ci2
GROUP BY b_unit,ndc
HAVING COUNT(concat(b_unit,ndc) )>1)";
//run query 2

bradgrafelman

Res wrote:
I'm almost certain this will still fail because you cannot run a subquery against a table that you are deleting from in the parent query.

Why is that?

The subquery is going to be evaluated first and its result set stored in a temporary table (e.g. in memory). The SQL server will then back out of the subquery and perform the DELETE statement, comparing the id values to those in the temporary table.

Res

I can't tell you why brad, but I can tell you it does. I was under the same assumption you were, then I spent 6 hours copying millions of records so I could run a dupe check on them. Also you have to run the delete multiple times if you have multiple dupes, as the group by is only going to return one of the dupe ids.

#1093 - You can't specify target table 'test' for update in FROM clause

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;


INSERT INTO `test` (`id`, `val`) VALUES
(1, 2),
(2, 2),
(3, 4),
(4, 5),
(5, 8),
(6, 2),
(7, 2),
(8, 4);


DELETE FROM test WHERE id IN (SELECT id FROM test GROUP BY val HAVING count(id) > 1);

While that delete statement gives the error above, the following does not and executes as expected.

CREATE TEMPORARY TABLE test_tmp select * from test;

DELETE FROM test WHERE id IN (SELECT id FROM test_tmp GROUP BY val HAVING count(id) > 1);

bradgrafelman

D'oh, looks like I skimmed over the MySQL manual page for DELETE a bit too quickly:

MySQL Manual wrote:
Currently, you cannot delete from a table and select from the same table in a subquery.

Seems odd that you basically have to do what I said above explicitly, but perhaps that's why the manual says "currently" (as if they were planning on implementing something of that nature later?).

Res

Sub-queries are the ugly step children in mysql. I'm not even sure we have proper index usage on them yet. I know that was one of the areas they were improving prior to the oracle buyout. At least support from 4.x to 5.x got way better. Memory is foggy but I think the issue stemmed from recalculating indexes once the first row had been deleted, which would require the subquery to be re-run, or something to that effect.

jrough

thanks for explaining the need for the 2nd table, very helpful