Java mysql, Simple update with PreparedStatement has syntax error

Question

This code has some sort of simple syntax error. I've fought it for hours now and I give up. Can you spot it? I bet it's easy. Thanks!

When I update just the firstname John, no problem. When I try to update the commented out line for the lastname too, syntax error.

import java.sql.*;

public class UpdateTester {

   public static void main(String[] args) {

      try {

         Connect connect = new Connect();
         Connection connection = connect.getConnection();

         try {

            String sql        = "UPDATE student SET firstName = ? "
                     + " WHERE studentID = 456987";

            //String sql     = "UPDATE student SET firstName = ? "
            //       + " Set lastName = ?, "
            //       + " WHERE studentID = 456987";

            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setString(1, "John");

            //pst.setString(2, "Johnson");

            pst.executeUpdate();
            System.out.println("Updated Successfully!");

            connection.close();

         } catch (SQLException e) {
            System.out.println("Exception 1!");
            e.printStackTrace();
         }
      } catch (Exception e) {
         System.out.println("Exception 2!");
         e.printStackTrace();
      }
   }
}

Column names are correct. Updating just the lastname on it's own works correctly too. Update fails with syntax error when trying to do both, as in the commented out lines.

Answer 1

3 issues:

• The SET keyword can only appear once in an UPDATE statement:
• Comma before second parameter missing
• Unnecessary comma before where clause

The corrected syntax:

String sql     = "UPDATE student SET firstName = ?, "
               + " lastName = ? "
               + " WHERE studentID = 456987";

SQL Reference