There are two classes named as Six and Seven. We will write one update command to change all class records of Six to Seven of class column.
After this update we will again display the number of records in each class.
import java.sql.*;
public class my_connect {
public static void main(String args[])
{
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/my_tutorial","root","test");
Statement st=con.createStatement();
// Displaying class with number of records
String query1="SELECT class, count(*) as no FROM student GROUP BY class";
ResultSet rs=st.executeQuery(query1);
// Listing class with number of records before the update
while(rs.next())
System.out.println(rs.getString(1)+" "+rs.getInt("no"));
//Update the records
String query2="UPDATE student set class='Seven' where class='Six'";
int no_rows=st.executeUpdate(query2);
System.out.println(no_rows + " rows updated..");
//isting of class with number of records after the update
String query3="SELECT class, count(*) as no FROM student GROUP BY class";
rs=st.executeQuery(query3);
// Listing class with number of records before the update
while(rs.next())
System.out.println(rs.getString(1)+" "+rs.getInt("no"));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}
The output is here
Eight 1
Five 3
Four 9
Nine 2
Seven 10
Six 7
Three 3
7 rows updated..
Eight 1
Five 3
Four 9
Nine 2
Seven 17
Three 3
You can see query1 is used to display the classes with number of rows before the update. query2 is used to update the records. Here all records with class=Six is updated to class=Seven. query3 is used to display the classes wit hnumber of rows after the update of records.
Note that the line int no_rows=st.executeUpdate(query2); is executing the query and returning the number of records updated, this number is stored in variable no_rows.
After updating once this code, you will not left with any records with class=Six so on next run there will be no rows matching the WHERE condition for updating. You can delete the student table and use the SQL dump ( given below ) to create the table again.