MySQL – Update Query
Table of content
- The MySQL UPDATE Statement
- Updating Data from the Command Prompt
- Updating Multiple Records from the Command Prompt
- Update Query into MySQL Database Using a Client Program
”;
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.
”;