Hiho hiho!
BORUSKUNG wrote:
>Hi all,
>
>I have a SQL question.
>If I want to delete all the duplicated record in a table,
>how can I do that in MySQL?
>I am using MySQL 3.23.49 on AIX 4.3.3
>
>there is a key field in the table
>
>what I want to do is check all the duplicated record by
>select order_no, count(*)
>from order
>group by order_no
>having count(*) > 1;
>
>then these duplicated rows can be all inserted into
>another table.
>but I don't know how to delete them from the original
>table.
>
>
I would do it without a table in between, you got a nice OS for that.
I'm using bash doing like this:
echo "SELECT concat('DELETE FROM order WHERE order_no=', order_no, '
LIMIT 1;') FROM order GROUP BY order_no HAVING copunt(*) > 1" | mysql -N
-u <user> <database> | mysql -N -u <user> <database>
That does the trick, IMHO nice and very easy.
Greetings
Ralf
--
Ralf Narozny
Splendid Internet GmbH
Skandinaviendamm 212, 24109 Kiel, Germany
fon: +49 431 660 97 0, fax: +49 431 660 97 20
mailto:[EMAIL PROTECTED], http://www.splendid.de
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
