java sql update query syntax error

Question

I want to update my data and delete it and replace it to another table for a single time. I don't know how to do this.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version right syntax to use near'(Cash,Change)values('9000','Your Change is:40.0')where Room='9' and Name' at line 1

 try{
    chge=jTextField27.getText();
    cash=jTextField4.getText();

    String SQLL = "update customer (Cash,Chnge) values ('" + cash + "','" + chge + "') where Room = ? and Name = ?;";     
    Dbase5 = conn.prepareStatement (SQLL); 
    Dbase5.setString(1,jTextField1.getText());
    Dbase5.setString(2,jTextField2.getText());
    int b = Dbase5.executeUpdate();

    ////////MOVE TO HISTORY   
    String SQL = "INSERT INTO history select * from customer where Room = ? and Name = ?";     
        Dbase = conn.prepareStatement (SQL);
        Dbase.setString(1,jTextField1.getText());
        Dbase.setString(2,jTextField2.getText());
        int rs=Dbase.executeUpdate();

       ///////MOVE TO HISTORY
    String MSQL = "delete from customer where Room=? and Name=?";
        Dbase1 = conn.prepareStatement (MSQL); 
        Dbase1.setString(1,jTextField1.getText());
        Dbase1.setString(2,jTextField2.getText());
        boolean s = Dbase1.execute();
        this.dispose();

Is that UPDATE statement really valid? Shouldn't it be something like update customer set Cash = '123', Chnge = 'foo' where Room = '42' and Name = 'John'? — Steve
– Steve, Commented Nov 2, 2016 at 19:06

Arjun Lajpal · Accepted Answer · 2016-11-02 18:49:42Z

0

All of the above queries are right except the update query:

    update customer (Cash,Chnge) values ('" + cash + "','" + chge + "') where Room = ? and Name = ?;

It should be like below:

    update customer set Cash = '" + cash + "', Chnge = '" + chge + "' where Room = ? and Name = ?;

or you may even set the first two arguments as well with preparedStatement:

    update customer set Cash = ?, Chnge = ? where Room = ? and Name = ?;

I would recommend you to go through the below link: http://dev.mysql.com/doc/refman/5.7/en/update.html

answered Nov 2, 2016 at 18:49

Arjun Lajpal

3081 silver badge11 bronze badges

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

Collectives™ on Stack Overflow

java sql update query syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related