467,175 Members | 1,313 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,175 developers. It's quick & easy.

update values on inserting duplicate index

hi

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

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
values.

I am wondering if this is possible with this command, or there are
other better choices?

thank you very much

Qianqian

Jul 23 '06 #1
  • viewed: 5502
Share:
4 Replies
>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
Jul 23 '06 #2
Gordon Burditt wrote:
>>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);

--
Michael Austin.
DBA Consultant
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)
Jul 23 '06 #3
>>>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);
That syntax won't work. Please state what it is you are trying
to accomplish.

Gordon L. Burditt
Jul 23 '06 #4
hi Gordon

indeed, REPLACE is exactly what I am looking for. thank you very much
for pointing out.

Qianqian

Gordon Burditt wrote:
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
Jul 23 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Richard Cornford | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
7 posts views Thread by Steve B. | last post: by
1 post views Thread by Muhammad Usman | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.