FUDforum: comp.lang.php » delete dupes using a temp table

Home » Imported messages » comp.lang.php » delete dupes using a temp table

Show: Today's Messages :: Polls :: Message Navigator

delete dupes using a temp table [message #171440]

Mon, 03 January 2011 20:55

jr
Messages: 4
Registered: January 2011

Karma:

Junior Member

This query gives me 487 duplicates.
SELECT bu,ndc,zonenm, COUNT(*)
from ci
GROUP BY bu,ndc
HAVING COUNT(concat(bu,ndc)) > 1

However,you cannot delete from the same table in a sub-query as the
main query.
In Oracle you can but not MySQL like below

DELETE FROM ci b
WHERE EXISTS ((
SELECT bu,ndc, count(1)
FROM ci
GROUP BY bu,ndc
HAVING COUNT(1)>1
) a
where a.bu=b.bu
and a.ndc = b.ndc
)

So my question is, how do you do this in MySQL?
I do have a unique id field.
I tried creating a temp table ci_tmp of the first select above of the
487 duplicates.
and using this DELETE query but get a runaway query and MySQL has to
be restarted.
So either this query below is wrong or I am using the wrong data in
the temp table.

DELETE FROM ci
WHERE id IN (select id
FROM ci_tmp
GROUP BY bu,ndc
HAVING COUNT(concat(bu,ndc) )>1)";

Report message to a moderator

[Message index]

		delete dupes using a temp table By: jr on Mon, 03 January 2011 20:55
		Re: delete dupes using a temp table By: Thomas 'PointedEars' on Mon, 03 January 2011 23:27
		Re: delete dupes using a temp table By: Luuk on Tue, 04 January 2011 09:20

Previous Topic:	PHP WEBSITE DEVELOPER REQUIRED
Next Topic:	Print PHP Manual

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 06 20:47:22 GMT 2024

Total time taken to generate the page: 0.04117 seconds