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.

i think the problem is the "," you must put it before lastname and remove the second set — Smartoop
– Smartoop, Commented Apr 4, 2013 at 23:13

Reimeus · Accepted Answer · 2013-04-04 23:36:36Z

10

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

edited Apr 4, 2013 at 23:36

answered Apr 4, 2013 at 23:15

Reimeus

160k16 gold badges224 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Java mysql, Simple update with PreparedStatement has syntax error

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related