List:General Discussion« Previous MessageNext Message »
From:Sebastian Mendel Date:April 22 2008 7:07am
Subject:Re: auto_increment
View as plain text  
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
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