FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » delete dupes using a temp table
Show: Today's Messages :: Polls :: Message Navigator
Return to the default flat view Create a new topic Submit Reply
delete dupes using a temp table [message #171440] Mon, 03 January 2011 20:55 Go to previous message
jr is currently offline  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)";
[Message index]
 
Read Message
Read Message
Read Message
Previous Topic: PHP WEBSITE DEVELOPER REQUIRED
Next Topic: Print PHP Manual
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Nov 26 23:44:08 GMT 2024

Total time taken to generate the page: 0.04619 seconds