INSERT, UPDATE & DELETE in SQL

While SELECT reads data, the INSERT, UPDATE, and DELETE statements change it. Together they make up the heart of SQL's Data Manipulation Language (DML). They are powerful — and a single missing WHERE clause can rewrite or wipe an entire table — so this guide covers both the syntax and the safety habits that keep you out of trouble.

INSERT: adding new rows

INSERT adds rows to a table. Always list the columns explicitly:

-- PostgreSQL / MySQL compatible

-- Insert one row, naming columns (the safe, clear style)
INSERT INTO student (student_id, full_name, city, age)
VALUES (1, 'Asha Patil', 'Jalgaon', 19);

-- Insert several rows in one statement
INSERT INTO student (student_id, full_name, city, age)
VALUES
    (2, 'Rohan Deshmukh', 'Pune',  20),
    (3, 'Meera Joshi',    'Nashik', 18);

-- Insert from a query: copy matching rows into another table
INSERT INTO alumni (student_id, full_name)
SELECT student_id, full_name
FROM student
WHERE age >= 21;

Naming the columns means your statement keeps working even if someone later adds a column to the table. Relying on column position (omitting the column list) is fragile.

UPDATE: changing existing rows

UPDATE modifies rows that match a condition. The WHERE clause is critical:

-- Change ONE student's city
UPDATE student
SET city = 'Mumbai'
WHERE student_id = 2;

-- Update multiple columns at once
UPDATE student
SET city = 'Mumbai', age = 21
WHERE student_id = 2;

-- Update many rows that match a condition
UPDATE student
SET age = age + 1
WHERE city = 'Jalgaon';

Danger: UPDATE student SET city = 'Mumbai'; with no WHERE changes every row in the table. There is no undo without a backup or transaction.

DELETE: removing rows

DELETE removes rows matching a condition:

-- Delete one specific row
DELETE FROM student
WHERE student_id = 3;

-- Delete a set of rows
DELETE FROM student
WHERE city = 'Nashik';

The same warning applies: DELETE FROM student; removes every row. Always include a WHERE unless you genuinely intend to empty the table.

DELETE vs TRUNCATE

To empty a whole table, you have two options:

-- DELETE: removes rows one by one, can be filtered, can be rolled back
DELETE FROM student;

-- TRUNCATE: removes all rows fast, cannot be filtered, resets some state
TRUNCATE TABLE student;

Key differences:

• DELETE is DML — it logs each row, can use a WHERE, and can be undone inside a transaction.
• TRUNCATE is DDL-like — it is much faster on big tables, cannot be filtered, and (in MySQL/InnoDB) auto-commits and resets AUTO_INCREMENT. In PostgreSQL TRUNCATE can be rolled back inside a transaction; in MySQL it generally cannot. This is a vendor difference worth remembering.

Safety habits that prevent disasters

1. Write the WHERE first. Type your WHERE clause before the SET or before running, so you never accidentally run an unfiltered statement.
2. Preview with a SELECT. Run SELECT * FROM student WHERE ... with the exact same condition first. If it returns the rows you expect, swap SELECT * for UPDATE/DELETE.
3. Wrap risky changes in a transaction. You can verify, then COMMIT or ROLLBACK:

-- Standard SQL transaction control
BEGIN;                              -- start a transaction

UPDATE student SET age = age + 1
WHERE city = 'Jalgaon';

-- Check the result with a SELECT here...
-- If it looks right:
COMMIT;                             -- make it permanent
-- If it looks wrong:
-- ROLLBACK;                        -- undo everything since BEGIN

Transactions are your safety net. See transactions & ACID for the full picture.

RETURNING: see what changed (PostgreSQL)

PostgreSQL lets you return the affected rows directly — handy for getting a generated ID:

-- PostgreSQL-specific: returns the row that was inserted
INSERT INTO student (full_name, city, age)
VALUES ('Sara Khan', 'Jalgaon', 19)
RETURNING student_id;

MySQL does not support RETURNING on INSERT the same way; you use LAST_INSERT_ID() instead. This is a clear vendor-specific difference.

Common mistakes

• Forgetting the WHERE clause. The number-one cause of data disasters. An unfiltered UPDATE or DELETE hits every row.
• Inserting into the wrong columns by position. Omitting the column list means a schema change silently maps values to the wrong columns. Always list columns.
• Assuming TRUNCATE behaves like DELETE everywhere. Rollback support and auto-increment reset differ between PostgreSQL and MySQL. Know your database.
• Violating constraints and ignoring the error. An INSERT that breaks a foreign key or unique constraint is rejected — that is the database protecting you. Read the error; do not disable the constraint.
• Updating a primary key. Changing a key value can cascade or break references. Prefer stable surrogate keys (see keys in DBMS).

FAQ

How do I undo a DELETE? If you ran it inside a transaction that you have not committed, ROLLBACK. Once committed, only a backup restores the data. This is why transactions matter.

Can I update rows based on another table? Yes — both UPDATE ... FROM (PostgreSQL) and multi-table UPDATE (MySQL) allow it, and subqueries work everywhere. The syntax differs by vendor.

Does INSERT auto-generate the ID? If the column is defined as identity/auto-increment, omit it from your column list and the database fills it in.

Keep learning

Pair this with SQL SELECT basics to read what you wrote, protect bulk changes with transactions & ACID, and guard your data with constraints in SQL.

Want to practise safe data modification on real tables? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.