Using SQL in Java to set values

java, sql

Solution

use `,` instead of multiple `SET`

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

the basic syntax of update is

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

Problem

Beginner's question: I'm trying to manipulate tuples from two different attributes in a relation using Java to process the SQL (for a university non-assessed practical). I have a relation with four attributes, including the name of a type of coffee, the sales it has that week and the total sales of the coffee. The code I am trying to create will update the sales and the total fields with new figures. It is meant to take both `totalSales` and `Sales` arrays, add them, then update both attributes in the relation accordingly. This is my code so far (I've cut out all 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, running this gives me an SQL syntax error, focusing on the first line (the `String updateTotalString`). I have a feeling it's to do with the fact I'm trying to do two sets in one line and have got the syntax wrong. Can anybody help me out?

Original source