MySQL Update Command


MySQL update command is used to make changes in the value of existing records of any database table. The update command is a part of the DML command of SQL. The general syntax of Update command is as follows
Syntax

UPDATE  
   SET columnName = newValue, ColumnName= NewValue.....
   [ where <condition> ]

NOTE: Where clause with the condition in the UPDATE command is optional. If the condition is missing from the update command then the changes will be applicable to the whole table.

Example
Update the Admission number of the students from the table STUDENT

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> select * from student;
+-------+--------+-------------+--------------------+
| admno | name   | father_name | address            |
+-------+--------+-------------+--------------------+
| 101   | rakesh | jagdish     | c-100 brij vihar   |
| 102   | udit   | ramesh sono | B-200 surya nagar  |
| 103   | mohit  | suresh sono | DB-240 surya nagar |
+-------+--------+-------------+--------------------+
3 rows in set (0.00 sec)

mysql> update student set admno = 105 where name='rakesh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+-------+--------+-------------+--------------------+
| admno | name   | father_name | address            |
+-------+--------+-------------+--------------------+
| 105   | rakesh | jagdish     | c-100 brij vihar   |
| 102   | udit   | ramesh sono | B-200 surya nagar  |
| 103   | mohit  | suresh sono | DB-240 surya nagar |
+-------+--------+-------------+--------------------+
3 rows in set (0.00 sec)

mysql> update student set marks =89, grade ='A', phone='9871812332';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from student;
+-------+--------+--------------------+-------+-------+------------+
| admno | name   | address            | marks | grade | phone      |
+-------+--------+--------------------+-------+-------+------------+
| 105   | rakesh | c-100 brij vihar   | 89.00 | A     | 9871812332 |
| 102   | udit   | B-200 surya nagar  | 89.00 | A     | 9871812332 |
| 103   | mohit  | DB-240 surya nagar | 89.00 | A     | 9871812332 |
+-------+--------+--------------------+-------+-------+------------+
3 rows in set (0.00 sec)
  • Used select statement to show the contents of the current table student
  • used inverted commas to check character type value
  • An applied condition so that the change must apply only on the single row.

Python Script to Update Record in MySQL Table

Python script can also be used to make changes in the Table. Try the following Python script to do the needful

import mysql.connector
conn = mysql.connector.connect(host='localhost',user='root',password='',database="school")
cursor = conn.cursor()
cursor.execute("update student set admno=105 where name ='rakesh'")
conn.close()
print('Record Updated successfully')

Output of the above code :
$ python -u "c:\Users\rakesh\Desktop\database_connection.py"
Record Updated successfully
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.