++++

Data Engineering

May 2026×10 min read

Learn how MySQL represents missing data, how to update and delete rows safely, and how ALTER TABLE changes an existing schema.

3. UPDATE, DELETE, NULL, and ALTER TABLE

Driptanil DattaSoftware Developer

UPDATE, DELETE, NULL, and ALTER TABLE

After you can create and insert data, the next step is changing existing data. This lesson covers:

NULL values.
UPDATE statements.
DELETE statements.
ALTER TABLE for schema changes.

Create the practice database and table

CREATE DATABASE companydb;
 
USE companydb;
 
CREATE TABLE employees (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(25),
  LastName VARCHAR(50),
  Email VARCHAR(100),
  HireDate DATE,
  Salary DECIMAL(10, 2)
);

This table already has one constraint: EmployeeID is the primary key, so each employee must have a unique ID.

Insert sample employees

INSERT INTO employees (EmployeeID, FirstName, LastName, Email, HireDate, Salary)
VALUES
  (1, "John", "Doe", "john.doe@example.com", "2020-01-15", 60000.00),
  (2, "Jane", "Smith", "jane.smith@example.com", "2019-03-22", 75000.00),
  (3, "Alice", "Johnson", "alice.johnson@example.com", "2021-07-30", 50000.00),
  (4, "Bob", "Brown", "bob.brown@example.com", "2018-11-12", 65000.00);

Read the table:

SELECT *
FROM employees;

Work with NULL values

NULL means the value is missing or unknown. It is not the same as an empty string and it is not the same as zero.

INSERT INTO employees
VALUES
  (5, "Krish", NULL, "krishnaik06@gmail.com", NULL, 55000.00);

Use IS NULL and IS NOT NULL. Do not use = NULL.

SELECT *
FROM employees
WHERE LastName IS NULL;
 
SELECT *
FROM employees
WHERE LastName IS NULL
   OR HireDate IS NULL;

Update rows

Always include a WHERE clause when you intend to update specific records.

UPDATE employees
SET LastName = "Naik"
WHERE EmployeeID = 5;

Update a numeric column using its existing value:

UPDATE employees
SET Salary = Salary + 10000
WHERE EmployeeID = 5;

Check the result:

SELECT *
FROM employees;

Delete rows

Delete a single employee:

DELETE FROM employees
WHERE EmployeeID = 1;

Delete multiple employees with a condition:

DELETE FROM employees
WHERE Salary < 66000;

Before running a destructive DELETE, preview the matching rows with SELECT.

SELECT *
FROM employees
WHERE Salary < 66000;

Add columns with ALTER TABLE

ALTER TABLE changes the structure of an existing table.

Add one column:

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(25);

Add multiple columns:

ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(25),
ADD COLUMN date_of_birth DATE;

Modify column types

Change a column definition with MODIFY COLUMN.

ALTER TABLE employees
MODIFY COLUMN phone_number VARCHAR(20);
 
ALTER TABLE employees
MODIFY COLUMN Salary INT;

After adding the new columns, inserts need values for the new table shape if you omit the column list.

INSERT INTO employees
VALUES
  (6, "Krish", NULL, "krishnaik06@gmail.com", NULL, 55000, "989373733", "C", "1989-09-09");

In production code, prefer an explicit column list for inserts. It makes schema changes safer.

Rename and drop columns

Rename middle_name to middleName:

ALTER TABLE employees
CHANGE COLUMN middle_name middleName VARCHAR(25);

Drop one column:

ALTER TABLE employees
DROP COLUMN date_of_birth;

Drop multiple columns:

ALTER TABLE employees
DROP COLUMN phone_number,
DROP COLUMN middleName;

Schema changes can affect application code, dashboards, and downstream pipelines. Treat ALTER TABLE as a planned change, not a casual cleanup command.

2. School Database Practice 4. DML Practice with customer_info