473,372 Members | 830 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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
4 5827
>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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: S.Patten | last post by:
Hi, I have a problem with updating a datetime column, When I try to change the Column from VB I get "Incorrect syntax near '942'" returned from '942' is the unique key column value ...
2
by: Richard Cornford | last post by:
Anyone who has taken a look at the online FAQ today may have noticed that I have updated it. The majority of the changes are the updating of broken links and the implementation of that extensive...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
9
by: Curtis Stanford | last post by:
I'm in a situation where I need to load an ASCII file into a database. No sweat so far right? The records are indexed by date and I have a unique index on date. The ASCII can overlap, meaning it...
7
by: Steve B. | last post by:
Does anyone know why the DA Update() would throw an exception? I moved the database but I updated the Conn and the DA, currently (trying)removing/replacing DS. Is there a another direction I...
1
by: Muhammad Usman | last post by:
hi, I have a table of student which contain four fields that are id(primary key number) , name(text),semester(text)and corse(text), When I insert values in a student table.Explorer Show the...
10
by: Roger Withnell | last post by:
I'm using ASP, VBScript and SQL Server. I'm also using UTF-8 character set and so my codepage is 65001 and SQL Server datatype nvarchar. I can insert unicode characters correctly into the...
3
by: Supremus | last post by:
G'day, SQL="INSERT INTO KQPlayers VALUES(" & _ "," & _ "'" & Request.Form("username") & "'," & _ (--snip--) Conn.Execute SQL This generates an error because my first field in the table...
6
by: aBheE | last post by:
i am trying to implement an application in order to keep rercords of the stationary of a company. i have a combo box named ComboP_Name In FormProducts form,which is getting the values from the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.