Updating multiple records in MySQL table from Eclipse and displaying data

In our student table, we will first list the number of students in each class by using group by query.

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.

Read part I of this tutorial on how to update single record.

MySQL MySQL student table dump

For Loop do while loop while loop if else switch Java- Tutorials

Java

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

MySQL Updating mulitple records

Subhendu Mohapatra