CODEWITHSUNDEEP
javamysqlsqljdbc
Razor22
Razor22

Reputation: 23

Whats wrong with this SQL clause?

I'm trying to update the column amount if the buyid (primary key) is a specific value.

UPDATE portfolio set amount=40 WHERE buyid=3

I work with JDBC and MySql, everytime I try to execute the statement i get the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'buyid=3' at line 1

table structure of portfolio:

buyid int
username varchar
stockname varchar
priceperstock float
amount int

Javasourcecode:

public void sellStock(int buyid, int amount, float currentprice, String user) {

...

    try {
        stmt = this.conn.createStatement();
        System.out.println(fetchedamount);
        System.out.println("UPDATE portfolio
                            SET amount=" + fetchedamount
                        + " WHERE buyid=" + buyid);

        stmt.execute("UPDATE stockman.portfolio
                      SET amount=" + fetchedamount
                   + "WHERE buyid=" + buyid+"");
        // update capital
        newmoney = amount * currentprice + oldmoney;

    } catch (SQLException ee) {
        ee.printStackTrace();
    }

Upvotes: 0

Views: 128

Answers (3)

Arnold0513
Arnold0513

Reputation: 1

Repleace the stm.execute line with: stmt.execute("UPDATE stockman.portfolio SET amount=" + fetchedamount + " WHERE buyid=" + buyid+" ");

Or: stmt.execute("UPDATE stockman.portfolio SET amount='" + fetchedamount + "' WHERE buyid='" + buyid+"' ");

Upvotes: 0

Chander .k
Chander .k

Reputation: 541

Just add space before Where it will solve the problem.

 stmt.execute("UPDATE stockman.portfolio
                  SET amount=" + fetchedamount
               + " WHERE buyid=" + buyid+"");

Upvotes: 0

xrcwrn
xrcwrn

Reputation: 5325

At stmt.execute() Your generated query is like
"UPDATE stockman.portfolio set amount=23WHERE buyid=54 "

Here 23Where is one whole string so you have to give space between these two.

Give space between " and Where like the following: 

" WHERE buyid="

And remove the last +""

Upvotes: 7

Related Questions