I'm trying to subtract a scanner input from a table value. Here's a screenshot of my table: Here's what my code looks like: The output of my code is: Here's what I want my table to look like after subtracting 100 (my scanner input quantity): I'm unsure as to why subtracting my scanner variable (quantity) doesn't work, and I would

Subtract scanner value from sql table value

I’m trying to subtract a scanner input from a table value. Here’s a screenshot of my table:

Table 1

Here’s what my code looks like:

    String url = "jdbc:mysql://localhost:3306/Project";
    String username = "x";
    String password = "y";

    try {
        Scanner scanner = new Scanner(System.in);

        System.out.println("What would you like to buy?");
        int purchase_id = scanner.nextInt();

        System.out.println("How many would you like to purchase?");
        int quantity = scanner.nextInt();

        Connection conn = DriverManager.getConnection(url, username, password);
        Statement myStmt = conn.createStatement();

        String sql = "update seattleBranch set inventory = " + quantity + "where item_id = 001";

        myStmt = conn.prepareStatement(sql);

        ((PreparedStatement) myStmt).executeUpdate();
    }

    catch (SQLException e) {
        e.printStackTrace();
    }
}

The output of my code is:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'item_id = 001' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
    at branchComms.main(branchComms.java:27)

Here’s what I want my table to look like after subtracting 100 (my scanner input quantity):
Table 2

I’m unsure as to why subtracting my scanner variable (quantity) doesn’t work, and I would appreciate any corrections to my code.

Answer

You are missing a space in the query before the where part, it should be like:

String sql = "update seattleBranch set inventory = " + quantity + " where item_id = 001";

Advertisement

Answer