how can I make my sql update statement work?

Question

My code won't update the database although I've tried changing every aspect of it to find the mistake please help I can print the set but I can't update and change it. I've tried prepareStatement and createStatement.

public void update() throws SQLException {
    try {
        PreparedStatement preparedStatement = connection.prepareStatement(
            "UPDATE main_table SET status=? WHERE ID=1"
        );
        preparedStatement.setInt(1, 1);
        preparedStatement.executeUpdate();
    }
    catch (SQLException e) {
        System.out.println("Could not update data to the database " + e.getMessage());
    }
}

Answer 1

I would also check your table for any constraints that might be stopping you, and while the code there looks to be functional we're not able to see your database connection.

But here's a working example that should hopefully highlight the cause of your issue 😁

I have a students table

Then I print out the table contents

public static void main(String[] args){

    /* Print only students who are 18 and over */
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/jdbc-example","root","password");
    String queryAges = "Select * from students where age >= ?";
    PreparedStatement agesStmt = conn.prepareStatement(queryAges);
    agesStmt.setInt(1, 18);
    ResultSet rs = agesStmt.executeQuery();

    while(rs.next()){
        System.out.printf("ID: %d, Name: %s, Age: %d\n",
            rs.getInt("id"),
            rs.getString("name"),
            rs.getInt("age"));
    }
    rs.close();
    agesStmt.close();
    conn.close();

}

Output

ID: 1, Name: Henry Holt, Age: 47
ID: 2, Name: Barry McLarry, Age: 32
ID: 5, Name: Gail Emmerson, Age: 24

Update a student's age then print that student record

public static void main( String[] args ) throws SQLException {

    /* Change Henry's age to 48 */
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/jdbc-example", "root", "password");
    String queryAges = "update students set age = ? where (name = ?)";
    PreparedStatement updateStmt = conn.prepareStatement(queryAges);
    updateStmt.setInt(1, 48);
    updateStmt.setString(2, "Henry Holt");
    int status = updateStmt.executeUpdate();
    if (status == 1) {
        PreparedStatement fetch = conn.prepareStatement("select * from students where name = ?");
        fetch.setString(1, "Henry Holt");
        ResultSet rs = fetch.executeQuery();
        while (rs.next())
            System.out.printf("ID: %d, Name: %s, Age: %d\n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("age"));
        rs.close();
    }
    updateStmt.close();
    conn.close();
}

Output

ID: 1, Name: Henry Holt, Age: 48

I hope that's helpful

Answer 2

Is the column status accepting NULL value in the table main_table? You can't input NULL value, in a column that not alow it