List:General Discussion« Previous MessageNext Message »
From:Hiep Nguyen Date:April 22 2008 11:19am
Subject:Re: auto_increment
View as plain text  
On Tue, 22 Apr 2008, Sebastian Mendel wrote:

> Sebastian Mendel schrieb:
>> Hiep Nguyen schrieb:
>>> hi list,
>>> 
>>> reading manual on mysql regarding auto_increment with multiple-column 
>>> index:
>>> 
>>> CREATE TABLE animals (
>>>     grp ENUM('fish','mammal','bird') NOT NULL,
>>>     id MEDIUMINT NOT NULL AUTO_INCREMENT,
>>>     name CHAR(30) NOT NULL,
>>>     PRIMARY KEY (grp,id)
>>> );
>>> 
>>> INSERT INTO animals (grp,name) VALUES
>>>     ('mammal','dog'),('mammal','cat'),
>>>     ('bird','penguin'),('fish','lax'),('mammal','whale'),
>>>     ('bird','ostrich');
>>> 
>>> SELECT * FROM animals ORDER BY grp,id;
>>> 
>>> +--------+----+---------+
>>> | grp    | id | name    |
>>> +--------+----+---------+
>>> | fish   |  1 | lax     |
>>> | mammal |  1 | dog     |
>>> | mammal |  2 | cat     |
>>> | mammal |  3 | whale   |
>>> | bird   |  1 | penguin |
>>> | bird   |  2 | ostrich |
>>> +--------+----+---------+
>>> 
>>> my question is what id would be if i:
>>> 
>>> UPDATE `animals` SET `grp`='mammal' WHERE `grp`='bird' AND `id`='2' AND 
>>> `name`='ostrich' LIMIT 1;
>> 
>> you do not need `name`='ostrich' in WHERE, cause grp,id are already UNIQUE
>> 
>> your key is grp,id (bird,2)
>> 
>> but your query will fail, because there is already grp,id (mammal,2) and 
>> therre can not be two identical UNIQUE (PRIMARY) keys
>
> auto_increment comes only in effect when inserting NULL (or 0 in some SQL 
> mode) or nothing (with default NULL, 0 what should be always the case for 
> auto_increment fields)
>
> your query should look like this:
>
> UPDATE `animals`
>   SET `grp` = 'mammal',
>       `id`  = NULL
> WHERE `grp` = 'bird'
>   AND `id`  = '2'
> LIMIT 1;
>
> -- 
> Sebastian Mendel
>

thanks, i got it.
Thread
auto_incrementHiep Nguyen21 Apr
  • Re: auto_incrementSebastian Mendel22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementHiep Nguyen22 Apr
  • Re: auto_incrementBen Clewett22 Apr
    • Re: auto_incrementSebastian Mendel22 Apr
      • Re: auto_incrementBen Clewett22 Apr
      • PerformanceBruno B .  B .  Magalhães22 Apr
        • Re: PerformancePhil22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformanceRob Wultsch22 Apr
        • Re: PerformancePerrin Harkins22 Apr
          • Re: PerformanceBruno B .  B .  Magalhães22 Apr
            • Re: PerformanceRob Wultsch22 Apr