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.
