Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- update values on inserting duplicate index
July 23, 2006, 3:42 am
rate this thread
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`)
because the third value pair, (2,9) was ignored due to duplication in
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:
after searching mysql online document, I only found the "on duplicate
update" clause can do something similar, however, all the examples only
show setting the duplicate record to somthing unrelated to the new
I am wondering if this is possible with this command, or there are
other better choices?
thank you very much
Re: update values on inserting duplicate index
I commonly use a query like:
insert into table(id, counter) values ($id, 1) on duplicate key
(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.
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum