Java SQL update statement is not updating in the table in spite of not getting errors

Question

public void updateFields(BorrowedBook borrowedBook) throws SQLException {
    Integer copiesInBorrow = new Integer(0);
    Integer availableCopies = new Integer(0);
    PreparedStatement pstmt;
    try {
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM library_students.book WHERE BookID=" + "'" + getBookID(borrowedBook)+ "'");
        if((rs.next())){
            copiesInBorrow=rs.getInt(11);
            availableCopies=rs.getInt(13);
        }
        pstmt = con.prepareStatement("UPDATE library_students.book SET CopiesInBorrow= ? AND AvailableCopies=? WHERE BookID=?");
        pstmt.setInt(1,++copiesInBorrow);
        pstmt.setInt(2,--availableCopies);
        pstmt.setString(3,getBookID(borrowedBook));
        pstmt.executeUpdate();
        rs.close();
        }catch (SQLException ex) {
            ex.printStackTrace();
        }
}

I'm not getting any errors and the executeUpdate(); is returning also 1 what is the problem?

And please don't select unrelated tags. The C language is totally irrelevant to your problem. I also fail to see the relevance of the workbench tag. Therefore I have removed them. — Some programmer dude
– Some programmer dude, Commented Jan 19, 2019 at 11:50

Mark Rotteveel · Accepted Answer · 2019-01-19 13:45:03Z

You have a syntax error, change to:

UPDATE library_students.book SET CopiesInBorrow= ?, AvailableCopies=? WHERE BookID=?

Don't use AND between the columns you want to update. You could use AND in the WHERE part to apply the conditions you want.

If you wanted something like this:

UPDATE library_students.book SET CopiesInBorrow= ? WHERE AvailableCopies=? AND BookID=?

then the use of AND would be valid.

From your code I see that you just want to increase the value of a column and decrease another one. You don't need to find first these values in the table and then make the changes.

You can execute this statement:

UPDATE library_students.book SET CopiesInBorrow = CopiesInBorrow + 1, AvailableCopies = AvailableCopies - 1 WHERE BookID = ?

So change to this:

public void updateFields(BorrowedBook borrowedBook) throws SQLException {
    PreparedStatement pstmt;
    try {
        Statement stmt = con.createStatement();
        pstmt = con.prepareStatement("UPDATE library_students.book SET CopiesInBorrow = CopiesInBorrow + 1, AvailableCopies = AvailableCopies - 1 WHERE BookID = ?");
        pstmt.setString(1, getBookID(borrowedBook));
        pstmt.executeUpdate();
        rs.close();
    }catch (SQLException ex) {
        ex.printStackTrace();
    }
}

Collectives™ on Stack Overflow

Java SQL update statement is not updating in the table in spite of not getting errors

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