DML: INSERT, UPDATE, DELETE · SQL & Databases

Data Manipulation Language changes the rows. The single most important habit here: never UPDATE or DELETE without a WHERE.

Data Manipulation Language (DML) changes the contents of tables: INSERT adds rows, UPDATE changes existing rows, and DELETE removes rows. These are the statements that can do real damage, so the discipline of always scoping them with WHERE is essential.

Add, change, remove

INSERT adds one or many rows; UPDATE ... SET ... WHERE changes the matching rows; DELETE ... WHERE removes the matching rows. The WHERE clause decides which rows are affected — omit it and you hit every row.

Insert rows

INSERT INTO departments (dept_id, dept_name, location)
VALUES (40, 'Finance', 'Delhi');

-- multiple rows at once
INSERT INTO departments (dept_id, dept_name, location) VALUES
  (50, 'Legal', 'Chennai'),
  (60, 'Ops',   'Hyderabad');

Update and delete (always with WHERE)

UPDATE employees
SET    salary = salary * 1.10     -- 10% raise
WHERE  dept_id = 10;             -- only department 10

DELETE FROM employees
WHERE  status = 'inactive';

⚡ The edge

Always include a WHERE on UPDATE and DELETE. Without it, UPDATE employees SET salary = 0 zeroes everyone, and DELETE FROM employees empties the table.
An UPSERT (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE) inserts a row or updates it if the key already exists — handy for 'insert or refresh' logic.

Worked example

Give every employee in department 10 a 10% raise.

This changes existing rows, so it's an UPDATE.
Set salary to salary * 1.10.
Scope it to department 10 with WHERE dept_id = 10 — never omit the WHERE.

Answer: UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 10;

Worked example

What happens if you run UPDATE employees SET salary = 50000; with no WHERE?

UPDATE with no WHERE matches every row in the table.
So every employee's salary is set to 50000, wiping out the real values.
This is the classic costly mistake — always add a WHERE (and test with SELECT first).

Answer: Every row is updated to 50000 — the WHERE clause is missing, so it affects the entire table.

⚠ Watch out

Missing WHERE on UPDATE/DELETE changes/removes every row — the most dangerous SQL mistake.
Test the WHERE with a SELECT first to see exactly which rows you'll affect.
Inserting a row that violates a constraint (FK, NOT NULL, UNIQUE) is rejected — by design.