List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 6 2001 2:31pm
Subject:Re: Duplicate of Everything in Table
View as plain text  
Are you checking the manual and following along, or just plugging in my
hints?

You have to specify a length for the text fields.
Your total key length must be less than 256 if I remember correctly.
Choose values that are long enough to be unique.
EX:

ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle(25),
Text(100))

Vigile wrote:
> 
> That gave me:
> 
> SQL-query:
> 
> ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle, Text
> )
> MySQL said: BLOB column 'PageTitle' used in key specification without a key
> length
> 
> I guess because PageTitle and Text are [text] fields I can't do that.
> 
> Ryan Shrout
> Production Manager
> Athlonmb.com
> http://www.athlonmb.com
> rshrout@stripped
> 
> ----- Original Message -----
> From: "Gerald Clark" <gerald_clark@stripped>
> To: "Vigile" <vigile@stripped>
> Cc: <mysql@stripped>
> Sent: Friday, April 06, 2001 9:54 AM
> Subject: Re: Duplicate of Everything in Table
> 
> > Sorry:
> > ALTER IGNORE TABLE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle, Text
> > )
> >
> > Vigile wrote:
> > >
> > > That syntax didn't work for me:
> > >
> > > SQL-query:
> > >
> > > ALTER IGNORE Pages ADD UNIQUE ( PageID, ArticleID, PageTitle, Text )
> > > MySQL said: You have an error in your SQL syntax near 'Pages ADD UNIQUE
> (
> > > PageID, ArticleID, PageTitle, Text )' at line 1
> > >
> > > Ryan Shrout
> > > Production Manager
> > > Athlonmb.com
> > > http://www.athlonmb.com
> > > rshrout@stripped
> > >
> > > ----- Original Message -----
> > > From: "Gerald Clark" <gerald_clark@stripped>
> > > To: "Vigile" <vigile@stripped>
> > > Cc: <mysql@stripped>
> > > Sent: Friday, April 06, 2001 9:34 AM
> > > Subject: Re: Duplicate of Everything in Table
> > >
> > > > Do an:
> > > >  alter ignore table add unique ( firstcol, nextcol, ... lastcol )
> > > >
> > > > This will create a unique index on all fields, and delete the
> > > > duplicates.
> > > > You might want to leave this index there to avoid this problem in the
> > > > future.
> > > >
> > > > Vigile wrote:
> > > > >
> > > > > > You're right.  Is there anyway around this hassle of having
> to do
> all
> > > the
> > > > > > different IDs?  Something like:
> > > > > >
> > > > > > DELETE FROM Page wheres articleid=articleid and
> pageid=pageid
> limit 1
> > > > > >
> > > > > > Would that work?
> > > > > >
> > > > > > Ryan Shrout
> > > > > > Production Manager
> > > > > > Athlonmb.com
> > > > > > http://www.athlonmb.com
> > > > > > rshrout@stripped
> > > > > >
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: "Thalis A. Kalfigopoulos" <thalis@stripped>
> > > > > > To: "Vigile" <vigile@stripped>
> > > > > > Cc: <mysql@stripped>
> > > > > > Sent: Friday, April 06, 2001 1:57 AM
> > > > > > Subject: Re: Duplicate of Everything in Table
> > > > > >
> > > > > >
> > > > > > > On Fri, 6 Apr 2001, Vigile wrote:
> > > > > > >
> > > > > > > > I have a table, that DOES NOT have a primary key,
> but is setup
> > > like
> > > > > > this:
> > > > > > > >
> > > > > > > >       Field Type Attributes Null Default Extra
> Action
> > > > > > > >       PageID  int(11)    No  0    Change Drop
> Primary Index
> Unique
> > > > > > > >       ArticleID  int(11)    No  0    Change Drop
> Primary Index
> > > Unique
> > > > > > > >       PageTitle  text    No      Change Drop
> Primary Index
> Unique
> > > > > > > >       Text  text    No      Change Drop Primary
> Index Unique
> > > > > > > >
> > > > > > > >
> > > > > > > > However, for some reason (I think I know why, but
> its not
> > > important) I
> > > > > > have two of every entry.  Such as this:
> > > > > > > >
> > > > > > > > 1    69    words words
> > > > > > > > 2    69    words words more
> > > > > > > > 3    69    words words most
> > > > > > > > 1    69    words words
> > > > > > > > 2    69    words words more
> > > > > > > > 3    69    words words most
> > > > > > > >
> > > > > > > > So, when I am calling info from this database, I
> get doubles
> of
> > > each
> > > > > > result.  How can I delete just one of each entry, so I am
> left
> with
> > > just:
> > > > > > > >
> > > > > > > > 1    69    words words
> > > > > > > > 2    69    words words more
> > > > > > > > 3    69    words words most
> > > > > > > >
> > > > > > > > I can't do the delete that I was thinking about:
> > > > > > > >
> > > > > > > > Delete FROM Pages Where Pageid=1 and articleid=69
> because that
> > > would
> > > > > > delete BOTH of them.  Doh!  Any help?
> > > > > > >
> > > > > > > How about:
> > > > > > > delete from tablename where id=1 limit 1;
> > > > > > >
> > > > > > > But you'd have to do it for id=1,2,3... Not so good.
> > > > > > >
> > > > > > > regards,
> > > > > > > thalis
> > > > > >
> > > > >
> > > > > _________________________________________________________
> > > > > Do You Yahoo!?
> > > > > Get your free @yahoo.com address at http://mail.yahoo.com
> > > > >
> > > >
> > ---------------------------------------------------------------------
> > > > > 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
> <mysql-thread70722@stripped>
> > > > > To unsubscribe, e-mail
> > > <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> > > > > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> > > >
> > > > ---------------------------------------------------------------------
> > > > 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
> <mysql-thread70754@stripped>
> > > > To unsubscribe, e-mail
> > > <mysql-unsubscribe-vigile=yahoo.com@stripped>
> > > > Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
> > >
> > > _________________________________________________________
> > > Do You Yahoo!?
> > > Get your free @yahoo.com address at http://mail.yahoo.com
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
Thread
Duplicate of Everything in TableVigile6 Apr
  • Re: Duplicate of Everything in TableGerald Clark6 Apr
  • Re: Duplicate of Everything in TableVigile6 Apr
  • Re: Duplicate of Everything in TableGerald Clark6 Apr
  • Re: Duplicate of Everything in TableVigile6 Apr
  • Re: Duplicate of Everything in TableGerald Clark6 Apr
    • Re: Duplicate of Everything in TableThalis A. Kalfigopoulos6 Apr
      • Re: Duplicate of Everything in TableGerald Clark6 Apr
        • Re: Duplicate of Everything in TableThalis A. Kalfigopoulos6 Apr