🗄️ Unit 4 – Database Concepts & SQL · Lesson 18
MySQL: UPDATE, DELETE & ALTER
Starting data
We use the same STUDENT table with the 7 rows inserted in the previous lesson (RollNo 101–107). The queries on this page run one after another, so each one sees the changes made by the earlier ones.
UPDATE – changing data
Syntax
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Give 2 grace marks to XI-B:
SQL
UPDATE STUDENT SET Marks = Marks + 2 WHERE Class = 'XI-B'; SELECT Name, Class, Marks FROM STUDENT WHERE Class = 'XI-B';
Output
+-------+-------+-------+ | Name | Class | Marks | +-------+-------+-------+ | Kabir | XI-B | 69.5 | | Diya | XI-B | 77.0 | +-------+-------+-------+
Update several columns of one student:
SQL
UPDATE STUDENT SET City = 'Agra', DOB = '2009-01-20' WHERE RollNo = 107; SELECT * FROM STUDENT WHERE RollNo = 107;
Output
+--------+-------+--------+------------+-------+-------+------+ | RollNo | Name | Gender | DOB | Class | Marks | City | +--------+-------+--------+------------+-------+-------+------+ | 107 | Rohan | M | 2009-01-20 | XI-A | 39.5 | Agra | +--------+-------+--------+------------+-------+-------+------+
Replace missing cities:
SQL
UPDATE STUDENT SET City = 'Not given' WHERE City IS NULL; SELECT Name, City FROM STUDENT;
Output
+--------+-----------+ | Name | City | +--------+-----------+ | Aarav | Jaipur | | Sana | Lucknow | | Kabir | Jaipur | | Diya | Pune | | Ishaan | Delhi | | Meher | Not given | | Rohan | Agra | +--------+-----------+
DELETE – removing rows
Syntax
DELETE FROM table_name WHERE condition;
SQL
DELETE FROM STUDENT WHERE Marks < 40; SELECT RollNo, Name, Marks FROM STUDENT;
Output
+--------+--------+-------+ | RollNo | Name | Marks | +--------+--------+-------+ | 101 | Aarav | 88.5 | | 102 | Sana | 92.0 | | 103 | Kabir | 69.5 | | 104 | Diya | 77.0 | | 105 | Ishaan | 54.0 | | 106 | Meher | 81.0 | +--------+--------+-------+
Rohan (39.5) has been removed. Without WHERE, all rows would be deleted, but the empty table would remain.
ALTER TABLE – changing structure
➕
ADDAdd a new column: ALTER TABLE t ADD col type;🔧
MODIFYChange type/size/constraint: ALTER TABLE t MODIFY col newtype;✏️
CHANGERename + retype: ALTER TABLE t CHANGE old new type;➖
DROPRemove a column: ALTER TABLE t DROP col;SQL
ALTER TABLE STUDENT ADD Email VARCHAR(40); DESCRIBE STUDENT;
Output
+--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | RollNo | int | NO | PRI | NULL | | | Name | varchar(30) | NO | | NULL | | | Gender | char(1) | YES | | NULL | | | DOB | date | YES | | NULL | | | Class | varchar(5) | YES | | XI-A | | | Marks | decimal(5,1) | YES | | NULL | | | City | varchar(20) | YES | | NULL | | | Email | varchar(40) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+
SQL
ALTER TABLE STUDENT MODIFY Name VARCHAR(40) NOT NULL; ALTER TABLE STUDENT CHANGE City HomeCity VARCHAR(25); ALTER TABLE STUDENT DROP Email; DESCRIBE STUDENT;
Output
+----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | RollNo | int | NO | PRI | NULL | | | Name | varchar(40) | NO | | NULL | | | Gender | char(1) | YES | | NULL | | | DOB | date | YES | | NULL | | | Class | varchar(5) | YES | | XI-A | | | Marks | decimal(5,1) | YES | | NULL | | | HomeCity | varchar(25) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+
Change a default value:
SQL
ALTER TABLE STUDENT ALTER Class SET DEFAULT 'XI-B'; INSERT INTO STUDENT (RollNo, Name) VALUES (108, 'Tara'); SELECT RollNo, Name, Class FROM STUDENT WHERE RollNo = 108;
Output
+--------+------+-------+ | RollNo | Name | Class | +--------+------+-------+ | 108 | Tara | XI-B | +--------+------+-------+
Adding keys with ALTER
A MARKS table needs a composite primary key (one student, one subject) and a foreign key to STUDENT:
SQL
CREATE TABLE MARKS (RollNo INT, Subject VARCHAR(20), Score INT); ALTER TABLE MARKS ADD PRIMARY KEY (RollNo, Subject); ALTER TABLE MARKS ADD FOREIGN KEY (RollNo) REFERENCES STUDENT(RollNo); DESCRIBE MARKS;
Output
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | RollNo | int | NO | PRI | NULL | | | Subject | varchar(20) | NO | PRI | NULL | | | Score | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+
To remove a primary key: ALTER TABLE MARKS DROP PRIMARY KEY;
DELETE vs DROP
SQL
DELETE FROM MARKS; -- removes all rows, table stays DROP TABLE MARKS; -- removes the table itself
SQL
SHOW TABLES;
Output
+------------------+ | Tables_in_school | +------------------+ | STUDENT | +------------------+
DELETE (DML)
- Removes rows (all or some, with WHERE)
- Table structure remains
- DELETE FROM MARKS;
DROP (DDL)
- Removes the whole table: structure + data
- Table no longer exists
- DROP TABLE MARKS;
Safety tips
- 1Run a SELECT with the same WHERE first to see which rows will change.
- 2Always check that UPDATE and DELETE have a WHERE clause.
- 3Use the primary key in WHERE to change exactly one row.
- 4Take a backup before ALTER or DROP on important tables.
Summary & practice
- UPDATE … SET … WHERE changes existing rows.
- DELETE FROM … WHERE removes rows; DROP TABLE removes the table.
- ALTER TABLE: ADD, MODIFY, CHANGE, DROP columns; ADD/DROP PRIMARY KEY; ALTER … SET DEFAULT.
✅ Practice
Write SQL to: (1) increase marks of all girls by 5%, (2) change the class of RollNo 105 to XI-C, (3) delete students of Pune, (4) add a column Phone CHAR(10), (5) rename column Marks to Percentage.
Answers: (1) UPDATE STUDENT SET Marks = Marks * 1.05 WHERE Gender = 'F'; (5) ALTER TABLE STUDENT CHANGE Marks Percentage DECIMAL(5,1);
Frequently Asked Questions
What happens if I forget WHERE in UPDATE or DELETE?
The change is applied to every row of the table. UPDATE STUDENT SET Marks = 0; sets everyone’s marks to 0, and DELETE FROM STUDENT; empties the table.
Is ALTER a DDL or DML command?
ALTER TABLE is DDL because it changes the structure (schema) of a table. UPDATE and DELETE are DML because they change data.
What is the difference between MODIFY and CHANGE in ALTER TABLE?
MODIFY changes the data type or constraints of a column but keeps its name. CHANGE can rename the column as well as change its type.
