Khóa học miễn phí MySQL – Update Query nhận dự án làm có lương

MySQL – Update Query

Table of content


The MySQL UPDATE Statement

The MySQL UPDATE Query is used to modify the existing records in a table. This statement is a part of Data Manipulation Language in SQL, as it only modifies the data present in a table without affecting the table”s structure.

Since it only interacts with the data of a table, the UPDATE statement needs to used cautiously. If the rows to be modified aren”t selected beforehand, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.

Therefore, to filter records that needs to be modified, MySQL always provides a WHERE clause. Using a WHERE clause, you can either update a single row or multiple rows.

The UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

Following is the SQL syntax of the UPDATE command to modify the data in the MySQL table −

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]
  • You can update one or more field altogether.
  • You can specify any condition using the WHERE clause.
  • You can update the values in a single table at a time.

The WHERE clause is very useful when you want to update the selected rows in a table.

Updating Data from the Command Prompt

This will use the SQL UPDATE command with the WHERE clause to update the selected data in an MySQL table.

Example

First of all, let us create a table named CUSTOMERS using the following CREATE query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query inserts 7 records in to the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to retrieve all the records present in the CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Here, we are using the SQL UPDATE query to update the NAME field in the CUSTOMERS table. It sets the name to ”Nikhilesh” for the row where the ”ID” is equal to 6.

UPDATE CUSTOMERS 
SET NAME = ''Nikhilesh'' 
WHERE ID = 6;

Output

The above query has excuted successfully without any errors.

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the name has replaced to ”Nikhilesh”, use the following query −

Select * from CUSTOMERS;

As we can see the output, the NAME with ID 6 has been updated −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Nikhilesh 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Updating Multiple Records from the Command Prompt

Using UPDATE statement, multiple rows and columns in a MySQL table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. Thus, only updating the values in those records.

Example

Now, let us update multiple records in the previously created CUSTOMERS table using the following query −

UPDATE CUSTOMERS
SET ADDRESS = ''Vishakapatnam''
WHERE ID = 6 OR ID = 3;

Output

The above query has excuted successfully without any errors.

Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

To verify whether the ADDRESS has replaced with ”Vishakapatnam” in ID = 6 and 3, use the following query −

Select * from CUSTOMERS;

As we can see the output, the NAME with ID 6 has been updated −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Vishakapatnam 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Nikhilesh 22 Vishakapatnam 4500.00
7 Muffy 24 Indore 10000.00

Updating a table Using a Client Program

In addition to update records in a table using the MySQL query, we can also perform the UPDATE operation on a table using a client program.

Syntax

Following are the syntaxes of this operation in various programming languages −

To update records of a table in MySQL Database through PHP program, we need to execute the UPDATE statement using the mysqli function query() as −

$sql="UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]";
$mysqli->query($sql);

To update records of a table in MySQL Database through Node.js program, we need to execute the UPDATE statement using the query() function of the mysql2 library as −

sql = "UPDATE table_name SET column1 = value1,
   column2 = value2, ... WHERE condition";
con.query(sql);

To update records of a table in MySQL Database through Java program, we need to execute the UPDATE statement using the JDBC function executeUpdate() as −

String sql="UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]";
statement.executeUpdate(sql);

To update records of a table in MySQL Database through Python program, we need to execute the UPDATE statement using the execute() function of the MySQL Connector/Python as −

update_query = "UPDATE table_name SET field1 = new-value1,
   field2 = new-value2 [WHERE Clause]"
cursorObj.execute(update_query);

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''root@123
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf(''Connected successfully.<br />'');

if ($mysqli->query(''UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 4'')) {
   printf("Table tutorials_tbl updated successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not update table: %s<br />", $mysqli->error);
}

$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";

$result = $mysqli->query($sql);
  
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
         $row["tutorial_id"], 
         $row["tutorial_title"], 
         $row["tutorial_author"],
         $row["submission_date"]);               
   }
} else {
   printf(''No record found.<br />'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Table tutorials_tbl updated successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
  });

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Updating a single record in the table
  sql = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3;"
  con.query(sql);

  //Selecting records from table
  sql = "SELECT * FROM tutorials_tbl"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
[
  {
    tutorial_id: 1,
    tutorial_title: ''Learn PHP'',
    tutorial_author: ''John Paul'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 2,
    tutorial_title: ''Learn MySQL'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-07-25T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-07-25T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UpdateQuery {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3";
            st.executeUpdate(sql);
            System.out.println("Update query executed successfully..!");
            String sql1 = "SELECT * FROM tutorials_tbl";
            rs = st.executeQuery(sql1);
            System.out.println("Table records: ");
            while(rs.next()) {
            	String tutorial_id = rs.getString("tutorial_id");
            	String tutorial_title = rs.getString("tutorial_title");
            	String tutorial_author = rs.getString("tutorial_author");
            	String submission_date = rs.getString("submission_date");
            	System.out.println("Id: " + tutorial_id + ", Title: " + tutorial_title + ", Author: " +  tutorial_author + ", Submission_date: " + submission_date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Output

The output obtained is as shown below −

Update query executed successfully..!
Table records: 
Id: 1, Title: Learn PHP, Author: John Paul, Submission_date: 2023-08-08
Id: 2, Title: Angular Java, Author: Abdul S, Submission_date: 2023-08-08
Id: 3, Title: Learning Java, Author: Sanjay, Submission_date: 2007-05-06
Id: 4, Title: Python Tutorial, Author: Sasha Lee, Submission_date: 2016-09-04
Id: 5, Title: Hadoop Tutorial, Author: Chris Welsh, Submission_date: 2023-08-08

import mysql.connector
import datetime
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
#Creating a cursor object 
cursorObj = connection.cursor()
update_query = "UPDATE tutorials_tbl SET tutorial_title = ''Learning Java'' WHERE tutorial_id = 3"
cursorObj.execute(update_query)
connection.commit()
print("Row updated successfully.")
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Row updated successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc