MySQL Updating mulitple records

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


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer