How do I update multiple columns in MySQL (Through Java)?

Question

So this is just a snippet of my code:

Sql = "update budgetreport" +
                " set sales="+salesText.getText()+
                " where quarter="+ qTracker+
                " set cogs="+cogsText.getText()+
                " where quarter="+ qTracker;
        try {
            myStmt.executeUpdate(Sql);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

My problem is, it says there is an SQL syntax. I ran it with just the first part:

Sql = "update budgetreport" +
                " set sales="+salesText.getText()+
                " where quarter="+ qTracker;
        try {
            myStmt.executeUpdate(Sql);
        } catch (Exception e1) {
            e1.printStackTrace();
        }

And it ran fine. But I do not under stand what to do in order to make my first snippet of code function. Thanks for taking your time to read/answer :)! ......

I don't know much about MySql, but it seems as if it should be two different update statements: one for sales and one for cogs. — Jashaszun
– Jashaszun, Commented Jul 7, 2015 at 21:54
Also you want to use preparedStatements, as building SQL this way will lead you eventually down a dark path — tim_yates
– tim_yates, Commented Jul 7, 2015 at 21:56
If your question is resolved, please mark one of the answers as accepted. — reaanb
– reaanb, Commented Jul 8, 2015 at 20:57

Reimeus · Accepted Answer · 2015-07-07 22:03:02Z

6

Only one SET keyword is allowed per update

String sql = "update budgetreport" +
                " set sales=?, cogs=?" + 
                " where quarter=?";

PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, salesText.getText());
...
preparedStatement.executeUpdate();

edited Jul 7, 2015 at 22:03

answered Jul 7, 2015 at 21:57

Reimeus

160k16 gold badges225 silver badges282 bronze badges

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

3 Comments

Wyatt Lowery Over a year ago

How does the "PreparedStament" object help? Just curious, thanks!

Reimeus Over a year ago

It protects against SQL Injection attacks as well takes care of quoting of character types

Wyatt Lowery Over a year ago

Ok, I didn't know that. I'll be sure to use it.

reaanb · Accepted Answer · 2015-07-07 21:57:10Z

0

Try this:

Sql = "update budgetreport" +
      " set sales="+salesText.getText()+
      ", cogs="+cogsText.getText()+
      " where quarter="+ qTracker;

Watch out for SQL injection attacks and errors, you shouldn't be concatenating with unchecked textbox contents.

answered Jul 7, 2015 at 21:57

reaanb

10.1k2 gold badges28 silver badges40 bronze badges

2 Comments

Wyatt Lowery Over a year ago

Gee I really feel like an idiot now. All I was missing was a comma!

Wyatt Lowery Over a year ago

Anyway, thanks for the response, I really appreciate it :)

Neil McGuigan · Accepted Answer · 2015-07-07 21:57:18Z

0

Use a prepared statement. It's far safer.

String sql = "update budgetreport set sales=? where quarter=?";

PreparedStatement statement = con.prepareStatement(sql);

statement.setString(1, salesText.getText());
statement.setString(2, qTracker.toString());

statement.executeUpdate();

answered Jul 7, 2015 at 21:57

Neil McGuigan

48.5k12 gold badges131 silver badges156 bronze badges

Comments

Aritra · Accepted Answer · 2015-07-07 22:00:55Z

-1

You should probably start here: http://www.w3schools.com/sql/sql_update.asp since your SQL syntax is incorrect. Try

update budgetreport set sales=?,cogs=? where quarter=?

The above should be a prepared statement. See: http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

Or, you could ease your life a bit by using some light-weight frameworks like this: http://www.sql2o.org/ which abstracts PreparedStatements and makes your code more readable.

answered Jul 7, 2015 at 22:00

Aritra

1,22412 silver badges20 bronze badges

Comments

m.hassaballah · Accepted Answer · 2015-07-07 22:02:47Z

-1

The syntax for the update query is incorrect so you need to change it>

UPDATE table

SET column1 = expression1,

column2 = expression2,
....

WHERE conditions;

for example

Sql =update budgetreport" + " set sales="+salesText.getText()+ ", cogs="+cogsText.getText()+ " where quarter="+ qTracker;"

answered Jul 7, 2015 at 22:02

m.hassaballah

2501 silver badge5 bronze badges

Collectives™ on Stack Overflow

How do I update multiple columns in MySQL (Through Java)?

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related