Updating of mysql database via java

Question

I have a program that will update every one minute. Each time it updates, it will either add the time, date, demand, or it will update the demand according to the same time and date. However, my current code does not allow me to do that. I've tried add another code to rectify this problem, but it doesn't work. Does anyone know how to add a constraint so that when it encounters the same data and time, but different demand, it will just update the db with the new demand. Instead of adding a new row, which contains the same time and date but different demand.

My original code:

preparedStatement = connect
.prepareStatement("insert into  testdb.emcsg values (?, ?, ? , ?, ?)");
preparedStatement.setInt(1, count+1);
preparedStatement.setString(2, d1);
preparedStatement.setString(3, d2);
preparedStatement.setString(4, d3);
preparedStatement.setString(5, d4);
preparedStatement.executeUpdate();

My edited code:

preparedStatement = connect
.prepareStatement("insert INTO testdb.emcsg values (?, ?, ? , ?, ?) ON DUPLICATE KEY UPDATE 5");
preparedStatement.setInt(1, count+1);
preparedStatement.setString(2, d1);
preparedStatement.setString(3, d2);
preparedStatement.setString(4, d3);
preparedStatement.setString(5, d4);
preparedStatement.executeUpdate();

This edited code came out with an error "You have an error in your SQL syntax;" Advice and help will be appreciated!

Did you check the syntax by manually executing a test query in the mysql console? — Thomas
– Thomas, Commented Feb 1, 2012 at 12:32
Hi, no, I used the syntax provided on this website dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — Eugene
– Eugene, Commented Feb 1, 2012 at 12:34
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) — Eugene
– Eugene, Commented Feb 1, 2012 at 12:35

JB Nizet · Accepted Answer · 2012-02-01 12:33:55Z

3

The documentation shows that you must have an assignment at the end of the clause:

insert into  testdb.emcsg (a, b, c, d, e) values (?, ?, ? , ?, ?) 
ON DUPLICATE KEY UPDATE e = ?

Note that it's a good practice to list the column names in an insert statement, rather than relying on the order they were defined, and on the maintainer to know this order.

answered Feb 1, 2012 at 12:33

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eugene Over a year ago

Hi JB Nizet, thanks for the prompt reply. I have a question to ask. This is updating is only possible on condition that one primary key is not duplicated with the same data. However, in my case, I want to set 3 keys must be the same, or else, the thing will be updated. Is it possible?

JB Nizet Over a year ago

If you have a unique index on this tuple, it should work. Or you could just issue a select query, and insert or update depending on the result of the select.

Eugene Over a year ago

Hmm..I'm still not able to get it. Maybe I could illustrate this out: Date Time Demand 01-Jan 01:00-02:00 500 01-Jan 02:00-03:00 600 However, I'm adding a new data, a new demand value 01-Jan 01:00-02:00 700 I want the system to update with the new demand value. However, it must be able to differentiate which row to update. In this case, there's a need for 2 unique key?

Christoffer · Accepted Answer · 2012-02-01 12:34:43Z

0

From the documentation it seems that you might need to reference the column name(s) you want to update. For instance (a,b,c,d,e should be replaced by your actual column names):

preparedStatement = connect
.prepareStatement("insert INTO testdb.emcsg(a,b,c,d,e) values (?, ?, ? , ?, ?) ON DUPLICATE KEY UPDATE e=VALUES(e)");
preparedStatement.setInt(1, count+1);
preparedStatement.setString(2, d1);
preparedStatement.setString(3, d2);
preparedStatement.setString(4, d3);
preparedStatement.setString(5, d4);
preparedStatement.executeUpdate();

answered Feb 1, 2012 at 12:34

Christoffer

2,12515 silver badges22 bronze badges

Collectives™ on Stack Overflow

Updating of mysql database via java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related