| delete dupes using a temp table [message #171440] |
Mon, 03 January 2011 20:55  |
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  |
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 |
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
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]