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

Re: delete dupes using a temp table [message #171450 is a reply to message #171440]

Tue, 04 January 2011 09:20

Luuk
Messages: 329
Registered: September 2010

Karma:

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

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 27 04:55:30 GMT 2024

Total time taken to generate the page: 0.04688 seconds