Whats wrong with this SQL clause?

Question

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();
 }

• Ankur Singhal: how about datatype of buyid, also if you can share table structure
• Phoenix: Are you able to run this query directly in mysql client? Is the data type of buyid numberic?
• Gordon Linoff: You might want to check the statement for hidden characters . . . spaces that are not really spaces, for instance (sometimes just re-typing the expression can fix some problems).
• Razib: The query looks OK. It seems the buyid is not a number.
• Razor22: if i run it from myphpadmin it works.

Answer 1

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 +""

Answer 2

Just add space before Where it will solve the problem.

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

Answer 3

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+"' ");