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
