❮ Previous

Next ❯

Updating and Deleting Data in MySQL

Modify and remove MySQL records using Python UPDATE and DELETE statements.

UPDATE Query
The UPDATE SQL command is used to modify existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Note:

Always use WHERE; otherwise, all records will be updated.

Update Student Age

This code updates the age of a specific student in the students table.

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Update age of student named 'komal'
cursor.execute("UPDATE students SET age=23 WHERE name='komal'")

conn.commit()
print(cursor.rowcount, "record(s) updated")
conn.close()

Explanation:
cursor.rowcount → Shows how many rows were updated

Update Multiple Records

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Update course for all students aged 25
cursor.execute("UPDATE students SET course='BCA' WHERE age=25")

conn.commit()
print(cursor.rowcount, "record(s) updated")

conn.close()

DELETE Query
The DELETE SQL command is used to remove records from a table.
Syntax:
DELETE FROM table_name
WHERE condition;

Note:

Always use WHERE, otherwise all rows will be deleted!

Delete Student Record

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Delete student named 'Ravi'
cursor.execute("DELETE FROM students WHERE name='Ravi'")

conn.commit()
print(cursor.rowcount, "record(s) deleted")

conn.close()

Delete Multiple Records

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Delete students aged 23
cursor.execute("DELETE FROM students WHERE age=23")

conn.commit()
print(cursor.rowcount, "record(s) deleted")

conn.close()

TRUNCATE vs DELETE

Feature	DELETE	TRUNCATE
Removes all rows	Can remove specific rows with WHERE	Removes all rows only
Slower	Slower for large tables	Faster for large tables
Can use WHERE	Yes	No
Resets auto-increment ID	No	Yes
Transaction support	Yes	Limited / Not supported in some DB

TRUNCATE Table

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="college_db"
)

cursor = conn.cursor()

# Remove all records from students table
cursor.execute("TRUNCATE TABLE students")

conn.commit()
print("All records removed and auto-increment reset")

conn.close()

❮ Previous

Next ❯

UPDATE Query