Be careful using REPLACE instead of UPDATE as it will destroy any fields in
the existing record that aren't in the update field set. Unless of course
you always plan on setting all the fields on every REPLACE.
REPLACE is basicly an INSERT which upon finding a duplicate will replace it.
So treat REPLACE as INSERT.
thanks,
-- Andrew
----- Original Message -----
From: "John Nagle" <[EMAIL PROTECTED]>
To: "Andrew Schmidt" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, July 12, 2001 2:58 PM
Subject: Re: UPDATE which changes nothing gets duplicate error - bug?
> That sounds like the bug. In my example, I specified all
> five primary keys in both the update and the WHERE part, so that
> matches the situation reported in the bug. Thanks.
>
> I changed my program to use a REPLACE instead of an UPDATE,
> which seems to have worked around the problem. Does that bug ever
> affect REPLACE?
>
> John Nagle
> Animats
>
> Andrew Schmidt wrote:
> >
> > In the change log for 3.23.29:
> >
> > Fixed a bug in UPDATE involving multi-part keys where one specified all
key
> > parts both in the update and the WHERE part. In this case MySQL could
try to
> > update a record that didn't match the whole WHERE part.
> >
> > I think that was fixed. Try upgrading your mysql server to the latest
> > 3.23.39 and try testing that bug.
> >
> > thanks,
> >
> > -- Andrew
> >
> > ----- Original Message -----
> > From: "John Nagle" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Thursday, July 12, 2001 1:07 PM
> > Subject: UPDATE which changes nothing gets duplicate error - bug?
> >
> > > We have a table "ticker" with five primary keys. Sometimes we
> > > do an UPDATE but don't change any values. This produces the
> > > error "ERROR 1062: Duplicate entry". The manual indicates that
> > > a no-change UPDATE is allowed. (ref page 583) Is this a bug,
> > > or a documentation error?
> > >
> > > There are no auto-increment fields involved.
> > >
> > > Server 3.23.28-gamma, running on Win2K, SP1.
> > >
> > > John Nagle
> > > Downside.com
> > >
> > > mysql> describe ticker;
> > >
> >
+------------------------+-------------------------------------------+------
> > +-----+------------+-------+---------------------------------+
> > > | Field | Type |
> > > Null | Key | Default | Extra | Privileges |
> > >
> >
+------------------------+-------------------------------------------+------
> > +-----+------------+-------+---------------------------------+
> > > | symbol | varchar(5)
> > > | | PRI | | | select,insert,update,references |
> > > | exchange_section | char(3)
> > > | | PRI | | | select,insert,update,references |
> > > | exchange | varchar(6)
> > > | | PRI | | | select,insert,update,references |
> > > | cik | bigint(10)
> > > | | MUL | 0 | | select,insert,update,references |
> > > | company_conformed_name | varchar(120)
> > > | | MUL | | | select,insert,update,references |
> > > | security_name | varchar(120)
> > > | | PRI | | | select,insert,update,references |
> > > | valid_from | date
> > > | | PRI | 0000-00-00 | | select,insert,update,references |
> > > | valid_to | date
> > > | | | 0000-00-00 | | select,insert,update,references |
> > > | data_source | varchar(6)
> > > | | | | | select,insert,update,references |
> > > | cik_confidence | smallint(1)
> > > | | | 9 | | select,insert,update,references |
> > > | security_class | enum('normal','bank','foreign','unknown') |
> > > YES | | unknown | | select,insert,update,references |
> > > | errors | text |
> > > YES | | NULL | | select,insert,update,references |
> > >
> >
+------------------------+-------------------------------------------+------
> > +-----+------------+-------+---------------------------------+
> > > 12 rows in set (0.00 sec)
> > >
> > > mysql> SELECT * FROM ticker WHERE symbol='ACSEF'
> > > -> AND exchange='NASDAQ'
> > > -> AND exchange_section='SCM'
> > > -> AND security_name='ACS-Tech80 Limited - Common Stock'
> > > -> AND valid_from='2001-05-24';
> > >
> >
+--------+------------------+----------+-----+------------------------+-----
>
> ------------------------------+------------+------------+-------------+---
--
> > -----------+----------------+-----------------------------------------+
> > > | symbol | exchange_section | exchange | cik | company_conformed_name
|
> > > security_name | valid_from | valid_to |
> > > data_source | cik_confidence | security_class |
> > > errors |
> > >
> >
+--------+------------------+----------+-----+------------------------+-----
>
> ------------------------------+------------+------------+-------------+---
--
> > -----------+----------------+-----------------------------------------+
> > > | ACSEF | SCM | NASDAQ | 0 |
|
> > > ACS-Tech80 Limited - Common Stock | 2001-05-24 | 2001-07-11 |
> > > EDGAR | 7 | unknown | Company name "ACS
TECH80
> > > LTD" not found |
> > >
> >
+--------+------------------+----------+-----+------------------------+-----
>
> ------------------------------+------------+------------+-------------+---
--
> > -----------+----------------+-----------------------------------------+
> > > 1 row in set (0.00 sec)
> > >
> > > mysql> UPDATE ticker SET
> > > symbol='ACSEF',exchange_section='SCM',exchange='NASDAQ',
> > > -> cik='0',company_conformed_name='',security_name='ACS-Tech80
> > > Limited - Common Stock',
> > > ->
> > >
security_class='unknown',valid_from='2001-05-24',valid_to='2001-07-11',
> > > -> data_source='EDGAR',errors='Company name "ACS TECH80 LTD" not
> > > found',cik_confidence='7'
> > > -> WHERE symbol='ACSEF'
> > > -> AND exchange='NASDAQ'
> > > -> AND exchange_section='SCM'
> > > -> AND security_name='ACS-Tech80 Limited - Common Stock'
> > > -> AND valid_from='2001-05-24';
> > > ERROR 1062: Duplicate entry 'ACSEF-NASDAQ-SCM-ACS-Tech80 Limited -
> > > Common Stock-2001-05-24' for key 4
> > >
> > > ---------------------------------------------------------------------
> > > 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
> > >
> > >
>
> ---------------------------------------------------------------------
> 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
>
>
---------------------------------------------------------------------
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
