List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 17 2001 8:07pm
Subject:Re: modifying table with sort/unique
View as plain text  
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, paul@stripped
Thread
modifying table with sort/uniqueJohn Hunter17 Jul
  • Re: modifying table with sort/uniqueAlexander Skwar17 Jul
  • Re: modifying table with sort/uniquePaul DuBois17 Jul
    • Re: modifying table with sort/uniqueJohn Hunter17 Jul
      • Re: modifying table with sort/uniquePaul DuBois17 Jul