Java to MySQL: How to update row with a value from variable?

Question

I have a table TotalSales on my database with columns Date(Primary) and Sales. I send query thru my Java Program.

I want to add a row if Date row not exists, and if Date row exists, the value on Sales will be updated. On update, the new value on Sales will be 'current value on Sales' + 'the value of variable totalBill'.

Lets say before execution: row under Sales = 0, after execution: row under Sales = Sales + totalBill;

I tried this code:

String query = "INSERT INTO TotalSales (Date, Sales) VALUES(date, totalBill)
                ON DUPLICATE KEY UPDATE Sales= VALUES(Sales)+VALUES(totalBill)";
st = con.prepareStatement(query);
st.execute();

But doesn't work:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'totalBill' in 'field list'

Can anyone help?

in here you have query totalBill for table column name. Try to modify it and make it to data binding. — zxcvc
– zxcvc, Commented Oct 7, 2014 at 5:31
BTW don't call your column Date. It is a reserved word, and too generic. Use SaleDate or similar. — Turophile
– Turophile, Commented Oct 7, 2014 at 5:32
So, you are sending that string query to the database engine for execution. When it tries to excecute it, how will it know the value of totalBill? Only your program knows that. You need to use a parameterised statement. As I was typing an answer MadProgrammer put one in which is what I was going to say. — Turophile
– Turophile, Commented Oct 7, 2014 at 5:33

MadProgrammer · Accepted Answer · 2014-10-07 05:27:19Z

3

You need to take advantage of the parameterised nature of PreparedStatements and bind the values you want to apply before you execute the statement, something like...

String query = "INSERT INTO TotalSales (Date, Sales) VALUES(?, ?)
                    ON DUPLICATE KEY UPDATE Sales= VALUES(Sales)+?";
st = con.prepareStatement(query);
st.setDate(1, date);
st.setLong(2, totalBill);
st.setLong(3, totalBill);

for example

Take a look at Using Prepared Statements for more details

answered Oct 7, 2014 at 5:27

MadProgrammer

348k22 gold badges243 silver badges379 bronze badges

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

3 Comments

Turophile Over a year ago

Note that st needs to be a PreparedStatement.

MadProgrammer Over a year ago

@Turophile Well, I assumed that preapreStatement was assigning to a PreparedStatement, which was from the OP's code, but fair point

impulse Over a year ago

Great! My data were all string though st.setString() and instead of Sales = VALUES(Sales) + ? , its Sales = Sales+?

Juned Ahsan · Accepted Answer · 2014-10-07 05:27:02Z

1

You are not appending the variable to your insert query, rather simply using a string. So change this:

String query = "INSERT INTO TotalSales (Date, Sales) VALUES(date, totalBill)
                ON DUPLICATE KEY UPDATE Sales= VALUES(Sales)+VALUES(totalBill)";

to

String query = "INSERT INTO TotalSales (Date, Sales) VALUES(" + date + "," + totalBill + ")
                ON DUPLICATE KEY UPDATE Sales= VALUES(Sales)+VALUES(totalBill)";

ADVICE: But for this case, you should learn to use PreparedStatement

answered Oct 7, 2014 at 5:27

Juned Ahsan

68.9k12 gold badges101 silver badges138 bronze badges

Collectives™ on Stack Overflow

Java to MySQL: How to update row with a value from variable?

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