List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 16 2002 10:05pm
Subject:Re: REPLACE with multiple UNIQUE fields and AUTO_INCREMENT
View as plain text  
At 23:56 +0200 6/16/02, Elizabeth Mattijsen wrote:
>$ mysql -V
>mysql  Ver 11.17 Distrib 3.23.49, for pc-linux-gnu (i686)
>
>Situation: a table with two UNIQUE fields, one of them with AUTO_INCREMENT.
>
>Using REPLACE with a NULL value on the AUTO_INCREMENT field twice, 
>with an identical value for the other UNIQUE field, causes the auto 
>increment to be executed even though there already _is_ a record 
>when the REPLACE is executed the second time.
>
>I would expect the second replace to not do an update of the 
>AUTO_INCREMENT field because there is already a record available.

That's not how it works.  You're replacing based on the name value, and
updating the other column.  Updating an AUTO_INCREMENT column with NULL
causes the next sequence number to be generated and assigned to the column.

>
>Please check the following statements and their output.
>========================================================================
>mysql> CREATE TABLE _test (
>  id INT UNSIGNED AUTO_INCREMENT,
>  name VARCHAR(255),
>  UNIQUE id (id),
>  UNIQUE name (name)
>);
>Query OK, 0 rows affected (0.01 sec)
>
>mysql> DESCRIBE _test;
>+-------+------------------+------+-----+---------+----------------+
>| Field | Type             | Null | Key | Default | Extra          |
>+-------+------------------+------+-----+---------+----------------+
>| id    | int(10) unsigned |      | PRI | NULL    | auto_increment |
>| name  | varchar(255)     | YES  | MUL | NULL    |                |
>+-------+------------------+------+-----+---------+----------------+
>2 rows in set (0.00 sec)
>
>mysql> REPLACE INTO _test VALUES (NULL,'name');
>Query OK, 1 row affected (0.00 sec)
>
>mysql> SELECT * FROM _test;
>+----+------+
>| id | name |
>+----+------+
>|  1 | name |
>+----+------+
>1 row in set (0.01 sec)
>
>mysql> REPLACE INTO _test VALUES (NULL,'name');
>Query OK, 2 rows affected (0.00 sec)
>
>mysql> SELECT * FROM _test;
>+----+------+
>| id | name |
>+----+------+
>|  2 | name |
>+----+------+
>1 row in set (0.00 sec)
>========================================================================
>
>Is this behaviour correct?  I would expect the result to be "1,name" 
>instead of "2,name".
>
>I was hoping to not have to do a SELECT to see if there is already a 
>record with the second UNIQUE field.  It seems that this is needed 
>after all and that REPLACE is not very useful in this situation.
>
>Elizabeth Mattijsen

Thread
REPLACE with multiple UNIQUE fields and AUTO_INCREMENTElizabeth Mattijsen16 Jun
  • Re: REPLACE with multiple UNIQUE fields and AUTO_INCREMENTPaul DuBois16 Jun
    • Re: REPLACE with multiple UNIQUE fields and AUTO_INCREMENTTod Harter17 Jun