List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:July 28 1999 5:41pm
Subject:Re: getting rid of duplicates
View as plain text  
Joel Bremson wrote:
> 
> Is there a simple way to remove exact duplicate rows from the database?
> 
> Joel3

Hi Joel

You have two choices:
1) If your row is less than 255 bytes wide and contains only up to 16 columns, then you
can create a UNIQUE key over all columns with:
ALTER TABLE tablename ADD UNIQUE (col1, col2, col3, ...);

2) Create a temporary table with the exact same column names and types as the table you
want to be unique.
Then insert the values from the original table into the temporary one with the IGNORE
option.
Finally DROP the original table and rename the temporary table to the original table name.
Example:
LOCK TABLES tempOrig WRITE, original WRITE;
CREATE TABLE tempOrig (col1 INT, col2 VARCHAR(20),col3 TEXT,...);
INSERT IGNORE INTO tempOrig SELECT * FROM original;
DROP TABLE original;
ALTER TABLE tempOrig RENAME original;
UNLOCK TABLES;


Tschau
Christian

Thread
Restrict Accesstoxalot27 Jul
  • Restrict Accesssinisa27 Jul
    • Re: Restrict AccessThimble Smith27 Jul
      • Re: Restrict AccessPaul DuBois27 Jul
        • Re: Restrict AccessMartin Ramsch27 Jul
          • Re: Restrict AccessPaul DuBois28 Jul
            • Re: Restrict AccessThimble Smith28 Jul
        • Re: Restrict Accesssinisa28 Jul
    • Re: Restrict Accesstoxalot28 Jul
      • On GET_LOCK ()sinisa28 Jul
        • Re: On GET_LOCK ()Benjamin Pflugmann28 Jul
          • Re: On GET_LOCK ()sinisa28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
          • Re: On GET_LOCK ()Jim Faucette28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
              • Re: On GET_LOCK ()Thimble Smith28 Jul
          • Re: On GET_LOCK ()Gerald Clark28 Jul
        • Re: On GET_LOCK ()Paul DuBois28 Jul
          • Re: On GET_LOCK ()sinisa28 Jul
            • Re: On GET_LOCK ()Paul DuBois28 Jul
  • Re: On GET_LOCK ()Scott Hess28 Jul
    • getting rid of duplicatesJoel Bremson28 Jul
    • Re: getting rid of duplicatesChristian Mack28 Jul
    • Re: On GET_LOCK ()sinisa29 Jul
      • Re: On GET_LOCK ()Paul DuBois29 Jul
    • Re: On GET_LOCK ()Scott Hess29 Jul
Re: On GET_LOCK ()toxalot28 Jul
  • Re: On GET_LOCK ()sinisa28 Jul
    • Re: On GET_LOCK ()Benjamin Pflugmann29 Jul
  • Re: On GET_LOCK ()Sasha Pachev29 Jul
    • Re: On GET_LOCK ()Benjamin Pflugmann31 Jul
Re: On GET_LOCK ()Thimble Smith29 Jul
  • Re: On GET_LOCK ()Fraser MacKenzie29 Jul
    • Re: On GET_LOCK ()Thimble Smith29 Jul
      • Re: On GET_LOCK ()Fraser MacKenzie29 Jul
Re: On GET_LOCK()R. Mentink31 Jul