Editing Data and Tables

Lesson 0009 — INSERT, UPSERT, UPDATE, DELETE, ALTER & TRUNCATE — ~18 min read

Reading is not enough. Real apps add new rows, change existing values, and remove what is no longer needed. WHERE decides which rows are touched — get it wrong and you change everything.

Every real application needs to add new information, update existing records, and remove outdated data. This chapter covers the three essential write operations — INSERT, UPDATE, DELETE — plus UPSERT, ALTER, and TRUNCATE, with the critical role of WHERE.

DML operations — INSERT, UPDATE, DELETE flow

1. INSERT — Adding New Rows

employees(
  emp_id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE,
  is_active BOOLEAN
)
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NULL,
  salary DECIMAL(10,2) NOT NULL,
  hire_date DATE NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT chk_salary_nonnegative CHECK (salary >= 0)
);
CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50),
  salary DECIMAL(10,2) NOT NULL,
  hire_date DATE NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT chk_salary_nonnegative CHECK (salary >= 0)
);

Task: Add Neha Sharma (301, Sales, 55000.00, 2024-03-11, TRUE). Best practice: always specify column order — relying on table order is risky if schema changes.

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(301, 'Neha Sharma', 'Sales', 55000.00, '2024-03-11', TRUE);
INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(301, 'Neha Sharma', 'Sales', 55000.00, '2024-03-11', TRUE);

Insert multiple rows — comma-separated:

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(302, 'Ishan Gupta',  'Engineering', 110000.00, '2023-08-20', TRUE),
(303, 'Priya Singh',  'Support',      45000.00, '2024-07-01', TRUE),
(304, 'Karan Joshi',  'Engineering',  95000.00, '2022-02-14', FALSE),
(305, 'Meera Nair',   '',             50000.00, '2025-03-01', TRUE),
(306, 'Omar Farooq',  NULL,           62000.00, '2025-04-18', TRUE),
(307, 'Ananya Das',   'Finance',      72000.00, '2024-10-05', TRUE);
INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(302, 'Ishan Gupta',  'Engineering', 110000.00, '2023-08-20', TRUE),
(303, 'Priya Singh',  'Support',      45000.00, '2024-07-01', TRUE),
(304, 'Karan Joshi',  'Engineering',  95000.00, '2022-02-14', FALSE),
(305, 'Meera Nair',   '',             50000.00, '2025-03-01', TRUE),
(306, 'Omar Farooq',  NULL,           62000.00, '2025-04-18', TRUE),
(307, 'Ananya Das',   'Finance',      72000.00, '2024-10-05', TRUE);
emp_idnamedepartmentsalaryhire_dateis_active
301Neha SharmaSales55000.002024-03-11TRUE
302Ishan GuptaEngineering110000.002023-08-20TRUE
303Priya SinghSupport45000.002024-07-01TRUE
304Karan JoshiEngineering95000.002022-02-14FALSE
305Meera Nair''50000.002025-03-01TRUE
306Omar FarooqNULL62000.002025-04-18TRUE
307Ananya DasFinance72000.002024-10-05TRUE

UPSERT — Insert or Update

Insert if new, otherwise update the existing row (based on PK/unique). “Insert the row if it is new; otherwise, update the existing row.”

Existing row: 301, Neha Sharma, Sales, 55000, 2024-03-11 — new feed wants 301, Raj Sharma, Eng, 57000, 2029-01-01.

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active)
VALUES (301, 'Raj Sharma', 'Eng', 57000.00, '2029-01-01', TRUE)
AS new_record
ON DUPLICATE KEY UPDATE
  name       = new_record.name,
  department = new_record.department,
  salary     = new_record.salary,
  hire_date  = new_record.hire_date,
  is_active  = new_record.is_active;
INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active)
VALUES (301, 'Raj Sharma', 'Eng', 57000.00, '2029-01-01', TRUE)
ON CONFLICT (emp_id) DO UPDATE SET
  name       = EXCLUDED.name,
  department = EXCLUDED.department,
  salary     = EXCLUDED.salary,
  hire_date  = EXCLUDED.hire_date,
  is_active  = EXCLUDED.is_active;
emp_idnamedepartmentsalaryhire_dateis_active
301Raj SharmaEng57000.002029-01-01TRUE
302Ishan GuptaEngineering110000.002023-08-20TRUE
303Priya SinghSupport45000.002024-07-01TRUE
304Karan JoshiEngineering95000.002022-02-14FALSE
305Meera Nair''50000.002025-03-01TRUE
306Omar FarooqNULL62000.002025-04-18TRUE
307Ananya DasFinance72000.002024-10-05TRUE

2. UPDATE — Changing Existing Rows

UPDATE Without WHERE — Dangerous

UPDATE employees
SET department = 'HR';
UPDATE employees
SET department = 'HR';

Every row becomes HR — no WHERE means all rows. Considered dangerous in production.

emp_idnamedepartmentsalaryhire_dateis_active
301Raj SharmaHR570002029-01-01TRUE
302Ishan GuptaHR1100002023-08-20TRUE
303Priya SinghHR450002024-07-01TRUE
304Karan JoshiHR950002022-02-14FALSE
305Meera NairHR500002025-03-01TRUE
306Omar FarooqHR620002025-04-18TRUE
307Ananya DasHR1080002024-10-05TRUE

UPDATE With WHERE — Safe

UPDATE employees
SET department = 'Eng'
WHERE emp_id = 305;
UPDATE employees
SET department = 'Eng'
WHERE emp_id = 305;

Only emp_id = 305 changes — the safe pattern.

Updating Multiple Columns

UPDATE employees
SET department = 'HR',
    salary = 65000
WHERE emp_id = 305;
UPDATE employees
SET department = 'HR',
    salary = 65000
WHERE emp_id = 305;

Both columns changed atomically in one statement — ensures consistency.

Conditional Mass UPDATE

UPDATE employees
SET salary = salary * 1.5
WHERE hire_date >= '2024-01-01';
UPDATE employees
SET salary = salary * 1.5
WHERE hire_date >= '2024-01-01';
emp_idnamedepartmentsalaryhire_dateis_active
301Raj SharmaHR85500.002029-01-01TRUE
302Ishan GuptaHR110000.002023-08-20TRUE
303Priya SinghHR67500.002024-07-01TRUE
304Karan JoshiHR95000.002022-02-14FALSE
305Meera NairHR97500.002025-03-01TRUE
306Omar FarooqHR93000.002025-04-18TRUE
307Ananya DasHR108000.002024-10-05TRUE

Only rows with hire_date ≥ 2024-01-01 get a 50% raise — uses existing value (salary * 1.5).

Why is UPDATE … WHERE emp_id = 305 safer than UPDATE … SET department='HR'?

3. DELETE — Removing Rows

DELETE a Single Row

DELETE FROM employees
WHERE emp_id = 305;
DELETE FROM employees
WHERE emp_id = 305;

Only emp_id = 305 is removed.

DELETE Multiple Rows Using IN

DELETE FROM employees
WHERE emp_id IN (301, 302);
DELETE FROM employees
WHERE emp_id IN (301, 302);

Batch deletion — both rows removed together.

DELETE Without WHERE — All Rows

DELETE FROM employees;
DELETE FROM employees;

No WHERE → every row deleted, but table structure remains.

Employees table after DELETE — empty but structure remains
emp_idnamedepartmentsalaryhire_dateis_active
No rows found

4. ALTER — Changing Table Structure

ALTER TABLE modifies structure without deleting data. Base table before ALTER (4 rows remain after prior deletes):

emp_idnamedepartmentsalaryhire_dateis_active
303Priya SinghHR67500.002024-07-01TRUE
304Karan JoshiHR95000.002022-02-14FALSE
306Omar FarooqHR93000.002025-04-18TRUE
307Ananya DasHR108000.002024-10-05TRUE

Add a New Column

ALTER TABLE employees
ADD COLUMN temp_check INT;
ALTER TABLE employees
ADD COLUMN temp_check INT;
emp_idnamedepartmentsalaryhire_dateis_activetemp_check
303Priya SinghHR67500.002024-07-01TRUENULL
304Karan JoshiHR95000.002022-02-14FALSENULL
306Omar FarooqHR93000.002025-04-18TRUENULL
307Ananya DasHR108000.002024-10-05TRUENULL

New column is NULL for existing rows.

Backfill with UPDATE:

UPDATE employees
SET temp_check = 10;
UPDATE employees
SET temp_check = 10;

Common in production after ADD COLUMN.

Drop a Column

ALTER TABLE employees
DROP COLUMN temp_check;
ALTER TABLE employees
DROP COLUMN temp_check;

Column and its data are permanently removed.

Rename a Table

ALTER TABLE employees
RENAME TO employees_v2;
ALTER TABLE employees
RENAME TO employees_v2;

Data and structure stay the same; future queries must use employees_v2.

Rename a Column

ALTER TABLE employees_v2
RENAME COLUMN temp_check TO temp_check_v2;
ALTER TABLE employees_v2
RENAME COLUMN temp_check TO temp_check_v2;

CHANGE vs MODIFY (MySQL)

ClauseWhat it doesName neededExample
CHANGERenames + redefines type/constraintsOld + new name + typeALTER TABLE employees CHANGE salary monthly_salary DECIMAL(10,2);
MODIFYChanges type/constraints, keeps nameOnly typeALTER TABLE employees MODIFY salary DECIMAL(12,2);

Use MODIFY when you only want to change the type; CHANGE risks accidental rename — many bugs come from this.

5. TRUNCATE — Fast Empty

TRUNCATE TABLE is DDL — removes all rows quickly, resets to empty, faster than DELETE without WHERE.

TRUNCATE TABLE employees;
TRUNCATE TABLE employees;
emp_idnamedepartmentsalaryhire_dateis_active
No rows found

Table remains, but 0 rows.

DELETE vs TRUNCATE vs DROP

FeatureDELETETRUNCATEDROP
What it removesSelected rows (or all)All rowsEntire table (structure + data)
WHERE allowed?YesNoNo
Table remains?YesYesNo
SpeedSlower (row-by-row)Faster (bulk wipe)Fast (removes object)
Best useSpecific recordsReset / clear quicklyRemove table permanently

Notes

INSERT / UPSERT :-

INSERT INTO employees (cols) VALUES (...), (...) — specify cols

UPSERT: MySQL ON DUPLICATE KEY UPDATE … = new_record.col; PG ON CONFLICT (pk) DO UPDATE SET … = EXCLUDED.col

UPDATE / DELETE :-

UPDATE without WHERE → all rows (dangerous); UPDATE … WHERE emp_id=305 → one row

SET col1='HR', col2=65000 → multi-col in one statement

Conditional: UPDATE SET salary=salary*1.5 WHERE hire_date>='2024-01-01'

DELETE FROM employees WHERE emp_id=305 → one; WHERE IN (301,302) → many; no WHERE → all rows but table stays

ALTER / TRUNCATE :-

ALTER TABLE ADD COLUMN temp_check INT → NULL; backfill UPDATE SET temp_check=10

DROP COLUMN temp_check → data lost; RENAME TO employees_v2; RENAME COLUMN

CHANGE salary monthly_salary DECIMAL(10,2) (rename+type, needs old+new); MODIFY salary DECIMAL(12,2) (type only)

TRUNCATE TABLE employees → all rows gone, 0 rows, faster than DELETE; DROP → table gone

DELETE (DML, WHERE, slow) vs TRUNCATE (DDL, no WHERE, fast) vs DROP (DDL, removes table)

Primary sources: MySQL INSERT · MySQL UPDATE · Postgres DML. Ask me anything that's unclear.

← Prev: String Functions Next: Merging Query Results →

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.