At 2:42 PM -0500 7/17/01, John Hunter wrote:
>I have a table in which I want to eliminate duplicates, where I define
>a duplicate to be any two rows with the same values on field1 and
>field2
>
>This gives me the sort I want
>SELECT * FROM this_table ORDER BY name, time;
>
>But in addition I would like to drop duplicates.
If name and time are both NOT NULL, you can create a PRIMARY KEY and tell
MySQL to drop the duplicates:
ALTER IGNORE this_table ADD PRIMARY KEY (name,time)
(The IGNORE is significant; without it, ALTER TABLE will issue an error
when it detects a duplicate.)
If name or time can contain NULL, you can create a UNIQUE index and
MySQL will drop all duplicates except those that contain NULL:
ALTER IGNORE this_table ADD UNIQUE (name,time)
After indexing, you can drop the index if you don't want it.
If the columns can contain NULL and you want some Perl code that doesn't
depend on indexing and that will remove all duplicates, reply to me offlist.
>
>Is there a way to modify the table in place to remove the duplicates?
>If not, how do I create a temporary table with duplicates suppressed,
>and then rename/copy this table to the original table name?
>
>Thanks,
>John Hunter
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
