Using SQL in Java to set values

Question

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?

as a side note; your SQL capitalisation is all backwards, table and field names are usually lowercase, while SQL keywords are usually uppercase. — lynks
– lynks, Commented Nov 13, 2012 at 14:57
@lynks: Thanks for the advice. I was using the Java syntax (which I THINK uses small first letter then capitals for new words when declaring methods. Hadn't even thought about SQL capitalisation. Thanks for bringing it up! — Andrew Martin
– Andrew Martin, Commented Nov 13, 2012 at 19:40

John Woo · Accepted Answer · 2012-11-13 14:55:03Z

7

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 ....

answered Nov 13, 2012 at 14:55

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

Andrew Martin Over a year ago

Brilliant, very helpful. Knew it was going to be a silly mistake!

PermGenError · Accepted Answer · 2012-11-13 14:56:50Z

1

your syntax for Update is wrong:

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

check MYSQL UPDATE SYNTAX

answered Nov 13, 2012 at 14:56

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

Comments

Roman C · Accepted Answer · 2016-03-07 20:03:28Z

1

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

In your query, you have used set keyword two times, that is not correct syntax.

Rather you should use a comma between multiple values to set.

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

edited Mar 7, 2016 at 20:03

Roman C

1

answered Nov 13, 2012 at 14:55

Rohit Jain

214k45 gold badges419 silver badges534 bronze badges

2 Comments

Roman C Over a year ago

answer is probably stolen from the opponent.

Rohit Jain Over a year ago

@RomanC.. Yeah, actually world war is going on, and I may die if I don't answer, so I steal. Seriously, what kind of thinking level your kind of people have.. And please check the timestamp of this answer and the others, and think of it, whether you can steal in such a small time period.

vkamayiannis · Accepted Answer · 2012-11-13 14:55:30Z

0

Your update string should be like:

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

answered Nov 13, 2012 at 14:55

vkamayiannis

7353 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using SQL in Java to set values

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related