The UPDATE query in SQL is used to modify existing records in a table. It allows you to update specific columns' values in one or more rows of a table. It's important to note that theUPDATEqueryaffects only the data,not the structure of the table.
Syntax
UPDATE tablename
SET column1 = "new_value", column2 = "new_value"
WHERE condition;
- tablename: The name of the table where the data will be updated.
- SET:Specifies the columns and their new values.
- WHERE: Determines which rows will be updated. If omitted, all rows in the table will be updated.
DATABASE IN USE:
Example 1: Update the Age of a Student
In this example, we'll update the age of a student named "Rishi Kumar" in theSTUDENTtable. We will change his age to23:
Pythonimportmysql.connector# Connecting to the Databasemydb=mysql.connector.connect(host="localhost",database="College",user="root",# Replace with your MySQL usernamepassword="your_password"# Replace with your MySQL password)# Create a cursor objectcs=mydb.cursor()# SQL query to update the age of a studentstatement="UPDATE STUDENT SET AGE = 23 WHERE Name = 'Rishi Kumar'"# Execute the update statementcs.execute(statement)# Commit the changes to the databasemydb.commit()# Disconnecting from the databasemydb.close()
Output:
MySQL UpdateExplanation:
- UPDATEquery modifies the AGE column for the student with the name "Rishi Kumar".
- mydb.commit() ensures that the changes are saved to the database.
- connection is closed withmydb.close() to release resources.
Example 2: Correcting the Spelling of a Student's Name
In this example, we will correct the spelling of the student's name from "SK Anirban" to "S.K. Anirban" in theSTUDENTtable:
Pythonimportmysql.connector# Connecting to the Databasemydb=mysql.connector.connect(host="localhost",database="College",user="root",password="your_password"# Replace with your MySQL password)# Create a cursor objectcs=mydb.cursor()# SQL query to update the name of the studentstatement="UPDATE STUDENT SET Name = 'S.K. Anirban' WHERE Name = 'SK Anirban'"# Execute the update statementcs.execute(statement)# Commit the changes to the databasemydb.commit()# Disconnecting from the databasemydb.close()
Output:
Explanation:
- UPDATEquery changes the name "SK Anirban" to "S.K. Anirban".
- mydb.commit() ensures the changes are saved.
- The connection is closed after the operation.