Using SQL in Java to set values

Question

Beginner question: I am trying to manipulate tuples of two different attributes in a relation using Java to process SQL (for a university that has not been practically evaluated). I have a relationship with four attributes, including the name of the type of coffee, the sales it has this week, and the total coffee sales.

The code I'm trying to create will update sales and full fields with new numbers. It is intended to use the arrays of totalSales and Sales , add them, and then update both attributes in an appropriate way.

This is my code so far (I have cut out all the obvious / unnecessary code):

 String updateTotalString = "update COFFEES set TOTAL = ? set SALES = ? where COF_NAME = ?"; String [] coffees = {"Columbian", "Earl Grey", "Kenyan", "Nescafe"}; int [] totalSales = {400,650,340,1000}; int[] sales = {50,75,100,100}; updateTotal = con.prepareStatement(updateTotalString); for (int i = 0; i < len; i++) { updateTotal.setInt(1, (sales[i] + totalSales[i])); updateTotal.setInt(2, sales[i]); updateTotal.setString(3, coffees[i]); updateTotal.executeUpdate(); } 

Unfortunately, at startup this gives me a SQL syntax error, focusing on the first line ( String updateTotalString ). I feel this is due to the fact that I am trying to make two sets on the same line and misunderstood the syntax.
Can anybody help me?

Answer 1

use , instead of several SET

 String updateTotalString = "update COFFEES set TOTAL = ?, SALES = ? where COF_NAME = ?"; 

basic update syntax is

 UPDATE tableName SET colA = '', colB = '', ..... WHERE .... 

Answer 2

 String updateTotalString = "update COFFEES set TOTAL = ? set SALES = ? where " + "COF_NAME = ?"; 

In your query, you used the set keyword twice, this is not the correct syntax.

Rather, you should use comma between multiple values ​​for installation.

 String updateTotalString = "update COFFEES set TOTAL = ?, SALES = ? where COF_NAME = ?"; 

Answer 3

your update syntax is incorrect:

 UPDATE `tableName` SET `col1` = ?, `col2` = ? WHERE somecondition; 

check MYSQL UPDATE SYNTAX

Answer 4

Your update line should look like this:

 String updateTotalString = "update COFFEES set TOTAL = ?, SALES = ? where COF_NAME = ?";