How to insert and update multiple rows to Sql database with same query?

tags:

views:

1428

answers:

1

+1 Q:

How to insert and update multiple rows to Sql database with same query?

I have counter for unique column in my table. Now I have list where I have column name (which is unique) and counter (which contains some integer value). I know that it is possible to insert multiple lines, I just could make query containing all those items on the list, but problem is that if there is already data in unique field.

In that case there is already this name and it has counter value, in this case I need to sum existing counter value to added alue and update that row. Is there any way to make all this with one Sql query? I use MySQL database and Java.

+1 A:

INSERT ON DUPLICATE KEY UPDATE

is the syntax you're looking for.

See here.

Carl Manaster 2009-05-01 13:42:20

How can I use that with multiple lines ?

2009-05-01 13:57:55

Should this work when there are ? for prepared statement ?INSERT INTO words (word, counter) VALUES(?, ?) ON duplicate KEY UPDATE counter=counter+?,VALUES(?, ?) ON duplicate KEY UPDATE counter=counter+?,VALUES(?, ?) ON duplicate KEY UPDATE counter=counter+?,VALUES(?, ?) ON duplicate KEY UPDATE counter=counter+?;

2009-05-01 14:09:52

I'm sorry, I don't understand the question. I don't have experience with prepared statements. Your question just repeatsVALUES(?, ?) ON DUPLICATE KEY UPDATE counter=counter+?several times; I'm not familiar with a multiple-VALUES clause syntax although it might work fine. If multiple-VALUES isn't permitted, then you could just repeat the query for each set of values. Or you could, I think, include ALL of the values in a single VALUES clause. Specific examples would help.

Carl Manaster 2009-05-01 14:30:13

ansaurus

tags:

views:

answers:

How to insert and update multiple rows to Sql database with same query?

related questions