update values on inserting duplicate index

>I am very new to mysql. I have a question about using the "on duplicate
>update" clause with insert command.

>my table "data" has two columns, field1 and field2, where field1 is the
>index and is "unique".
>when I run
>insert ignore into `data` ( `field1`,`field2`)
> values
> (1,2)
> (2,6)
> (2,9)
> (5,1)
>I got
> 1,2
> 2,6
> 5,1
>because the third value pair, (2,9) was ignored due to duplication in
>field1.
>here is what I want to do: when inserting new record with duplicate
>index, I want the existing record gets updated. in the above case, I
>want the output look like the following after the insert command:
> 1,2
> 2,9
> 5,1

>>I am very new to mysql. I have a question about using the "on duplicate
>>update" clause with insert command.
>
>
> I commonly use a query like:
>
>         insert into table(id, counter) values ($id, 1) on duplicate key
>         update counter=counter+1;
>
> (where $id gets a value substituted in to it). Every time I want to
> count the occurrence of a $id, I run that query. If there's no record,
> it makes one. If there is a record, it increments the counter.
>
>
>>my table "data" has two columns, field1 and field2, where field1 is the
>>index and is "unique".
>>when I run
>>insert ignore into `data` ( `field1`,`field2`)
>>values
>> (1,2)
>> (2,6)
>> (2,9)
>> (5,1)
>>I got
>>1,2
>>2,6
>>5,1
>>because the third value pair, (2,9) was ignored due to duplication in
>>field1.
>>here is what I want to do: when inserting new record with duplicate
>>index, I want the existing record gets updated. in the above case, I
>>want the output look like the following after the insert command:
>>1,2
>>2,9
>>5,1
>
>
> I don't understand how you got to the result from the original.
>
> insert into `data` (field1, field2) values (2, 9) on duplicate key
> update field2 = field2 + 3;
>
> would accomplish what you stated but how you get there is unclear.
>
> Incidentally, if what you want to do is REPLACE the whole record,
> whether it existed previously or not, you can use REPLACE instead
> of INSERT and forget ON DUPLICATE KEY UPDATE.
>
>                                         Gordon L. Burditt

>>>I am very new to mysql. I have a question about using the "on duplicate
>>>update" clause with insert command.
>>
>>
>> I commonly use a query like:
>>
>>         insert into table(id, counter) values ($id, 1) on duplicate key
>>         update counter=counter+1;
>>
>> (where $id gets a value substituted in to it). Every time I want to
>> count the occurrence of a $id, I run that query. If there's no record,
>> it makes one. If there is a record, it increments the counter.
>>
>>
>>>my table "data" has two columns, field1 and field2, where field1 is the
>>>index and is "unique".
>>>when I run
>>>insert ignore into `data` ( `field1`,`field2`)
>>>values
>>> (1,2)
>>> (2,6)
>>> (2,9)
>>> (5,1)
>>>I got
>>>1,2
>>>2,6
>>>5,1
>>>because the third value pair, (2,9) was ignored due to duplication in
>>>field1.
>>>here is what I want to do: when inserting new record with duplicate
>>>index, I want the existing record gets updated. in the above case, I
>>>want the output look like the following after the insert command:
>>>1,2
>>>2,9
>>>5,1
>>
>>
>> I don't understand how you got to the result from the original.
>>
>> insert into `data` (field1, field2) values (2, 9) on duplicate key
>> update field2 = field2 + 3;
>>
>> would accomplish what you stated but how you get there is unclear.
>>
>> Incidentally, if what you want to do is REPLACE the whole record,
>> whether it existed previously or not, you can use REPLACE instead
>> of INSERT and forget ON DUPLICATE KEY UPDATE.
>>
>>                                         Gordon L. Burditt
>Could you also use:
>INSERT INTO table (f1,f2) VALUES (1,2) (2,6) (2,9) (5,1)
> ON DUPLICATE KEY UPDATE f2=VALUES(f2);

> Incidentally, if what you want to do is REPLACE the whole record,
> whether it existed previously or not, you can use REPLACE instead
> of INSERT and forget ON DUPLICATE KEY UPDATE.
>
>                                         Gordon L. Burditt