CODEWITHSUNDEEP
javamysqljdbc
Lakshya Munjal
Lakshya Munjal

Reputation: 77

Update data in table

The thing i want to achieve here is that i have a table name total_product in mysql database and i want to retrieve the value of SNo = 1 from the table and update the Quantity in the table. There is a text box i am using in GUI in which the additional product produced will be written. The output from the table is stored in variable id and the new quantity that is produced is stored in the variable q1. so the new product quantity will be q1 = q1 + id. I am not able to understand what should i put in the sql statement that is used in stmt.executeUpdate(sql) because the sql is a string and i have to pass an integer value to Qunty in the sql string.

Please Help.

    Connection conn = null ;
    Statement stmt = null ;

    String url = "jdbc:mysql://localhost:3306/project";
    String user = "root";
    String password = ".dpadpep";
    try {

        Class.forName("com.mysql.jdbc.Driver");

        conn = DriverManager.getConnection(url,user,password);

        String sql = "Select Qunty from total_product " + "where SNo = 1";

        stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(sql);

        int id=0;
        int q1 = Integer.parseInt(fld1[0].getText());

        while(rs.next()) {
            id = rs.getInt(1);
            System.out.println("Quantity="+id);
        }

        q1 = q1+id;

        sql = "UPDATE total_product " + "set Qunty = q1 where SNo=1";
        stmt.executeUpdate(sql);

Upvotes: 0

Views: 38

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

You don't need to explicitly retrieve the current value in the database, you can simply add the additional amount directly:

int q1 = Integer.parseInt(fld1[0].getText());
String sql = "UPDATE total_product SET Qunty = Qunty + ? WHERE SNo = 1";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, q1);
ps.executeUpdate();

Upvotes: 1

Related Questions