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
Switch to threaded view of this topic Create a new topic Submit Reply
delete dupes using a temp table [message #171440] Mon, 03 January 2011 20:55 Go to next message
jr is currently offline  jr
Messages: 4
Registered: January 2011
Karma: 0
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)";
Re: delete dupes using a temp table [message #171446 is a reply to message #171440] Mon, 03 January 2011 23:27 Go to previous messageGo to next message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
jr wrote:

> So my question is, how do you do this in MySQL?

I would tell you to RTFMySQLM or ask in a MySQL newsgroup, where
this is on-topic, but you would not listen. Or would you?


PointedEars
--
realism: HTML 4.01 Strict
evangelism: XHTML 1.0 Strict
madness: XHTML 1.1 as application/xhtml+xml
-- Bjoern Hoehrmann
Re: delete dupes using a temp table [message #171450 is a reply to message #171440] Tue, 04 January 2011 09:20 Go to previous message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
On 03-01-11 21:55, jr wrote:
> 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)";

if you tried to accomplish that with PHP, than post your code here, some
people here know how to solve these kind of bugs in PHP


--
Luuk
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: PHP WEBSITE DEVELOPER REQUIRED
Next Topic: Print PHP Manual
Goto Forum:
  

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

Current Time: Wed Nov 27 01:47:24 GMT 2024

Total time taken to generate the page: 0.02529 seconds