UPDATE SQL query is used to update any record in MySQL table.
Below we have the basic syntax of the UPDATE statement:
UPDATE table_name SET column_name = new_value WHERE condition
The above syntax is used to update any specific row in the MySQL table. And to specify which specific row of data we want to update, we use the
WHERE clause to provide the condition to be matched while looking for the right row of data to update.
UPDATETable Data: Example
Let us update the record in the students table (from the Python MySQL create table tutorial) by changing the name of the student whose rollno is 3. The code is given below:
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() ## defining the Query query = "UPDATE students SET name = 'Navin' WHERE rollno = 3" ## executing the query cursor.execute(query) ## final step is to commit to indicate the database ## that we have changed the table data db.commit()
To check if the data is updated successfully we can retrieve the students table data using the given below code:
import mysql.connector as mysql db = mysql.connect( host = "localhost", user = "yourusername", passwd = "yourpassword", database = "studytonight" ) cursor = db.cursor() ## defining the Query query = "SELECT * FROM students" ## getting records from the table cursor.execute(query) ## fetching all records from the 'cursor' object records = cursor.fetchall() ## Showing the data for record in records: print(record)
The output of the above code is:
('Ramesh', 'CSE', '149 Indirapuram', 1) ('Peter', 'ME', 'Noida', 2) ('Navin', 'CE', 'New Delhi', 3)
Here is the snapshot of the actual output:
SELECT query too, we can use the
WHERE clause to retrieve only the data of the row with rollno 3 or any other condition.