Changing Data

INSERT, UPDATE, and DELETE change rows. Every example here works on copies of the sample tables, so the original data stays intact for later lessons.

SQL

CREATE TABLE emp_copy AS SELECT * FROM employees;
ALTER TABLE emp_copy ADD PRIMARY KEY (id);
CREATE TABLE dept_copy AS SELECT * FROM departments;
ALTER TABLE dept_copy ADD PRIMARY KEY (id);
SELECT count(*) AS emp_rows FROM emp_copy;

Output

 emp_rows 
----------
       12
(1 row)

CREATE TABLE ... AS copies the rows and column types but not constraints, defaults, or indexes, which is why the primary keys are added afterward.

Example 1: INSERT one row, and DEFAULT

Name the columns. Columns left out get their default, or NULL when there is none. DEFAULT can also be written explicitly.

SQL

CREATE TABLE audit_log (
    id      serial PRIMARY KEY,
    action  text NOT NULL,
    note    text,
    logged  date DEFAULT DATE '2026-01-01'
);

INSERT INTO audit_log (action) VALUES ('start');
INSERT INTO audit_log (action, note, logged) VALUES ('load', DEFAULT, '2026-02-01');
SELECT * FROM audit_log ORDER BY id;

Output

 id | action | note |   logged   
----+--------+------+------------
  1 | start  |      | 2026-01-01
  2 | load   |      | 2026-02-01
(2 rows)

Always list the columns. INSERT INTO t VALUES (...) breaks when a column is added or reordered.

Example 2: multi-row INSERT and RETURNING

One statement with several rows is a single round trip and a single transaction. RETURNING sends back the inserted rows, including generated values such as serial ids.

SQL

INSERT INTO audit_log (action, note)
VALUES ('a', 'first'), ('b', 'second'), ('c', NULL)
RETURNING id, action;

Output

 id | action 
----+--------
  3 | a
  4 | b
  5 | c
(3 rows)

RETURNING also works with UPDATE and DELETE. Oracle uses RETURNING ... INTO, SQL Server uses OUTPUT, and MySQL has no equivalent.

Example 3: INSERT ... SELECT

Insert the rows produced by a query. Copy Sales employees earning at least 90000 into a new department:

SQL

INSERT INTO dept_copy (id, name) VALUES (6, 'Sales Leads');

INSERT INTO emp_copy (id, name, dept_id, manager_id, salary, hired_on)
SELECT id + 100, name || ' (lead)', 6, manager_id, salary, DATE '2026-01-01'
FROM employees
WHERE dept_id = 2 AND salary >= 90000
RETURNING id, name, dept_id;

Output

 id  |     name      | dept_id 
-----+---------------+---------
 106 | Divya (lead)  |       6
 107 | Sameer (lead) |       6
(2 rows)

Example 4: UPDATE with WHERE and expressions

SET can use expressions that read the row's current values. The right side always sees the old values, so swaps work.

SQL

UPDATE emp_copy
SET salary = salary * 1.10,
    name = upper(name)
WHERE dept_id = 3
RETURNING id, name, salary;

Output

 id |  name  | salary 
----+--------+--------
  9 | VIKRAM | 132000
 10 | ANITA  |  88000
(2 rows)

The salary * 1.10 result is stored as int, so the value is rounded to a whole number. RETURNING shows the new values.

Example 5: UPDATE ... FROM (update using a join)

Set each employee's salary from another table. FROM adds tables to the update; the WHERE clause links them. A target row that matches no joined row is left alone. If it matches several, one arbitrary match is used.

SQL

UPDATE emp_copy e
SET salary = s.new_salary
FROM (VALUES (2, 190000), (3, 155000)) AS s(id, new_salary)
WHERE e.id = s.id
RETURNING e.id, e.name, e.salary;

Output

 id | name  | salary 
----+-------+--------
  2 | Ravi  | 190000
  3 | Kiran | 155000
(2 rows)

SQL Server and MySQL write this as UPDATE ... JOIN. Oracle uses MERGE or a correlated subquery.

Example 6: DELETE with a subquery and USING

Delete rows chosen by a subquery:

SQL

DELETE FROM emp_copy
WHERE dept_id IN (SELECT id FROM dept_copy WHERE name = 'Sales Leads')
RETURNING id, name;

Output

 id  |     name      
-----+---------------
 106 | Divya (lead)
 107 | Sameer (lead)
(2 rows)

USING joins other tables into a delete, like FROM in an update:

SQL

DELETE FROM emp_copy e
USING dept_copy d
WHERE e.dept_id = d.id
  AND d.name = 'HR'
RETURNING e.name AS deleted_employee, d.name AS department;

Output

 deleted_employee | department 
------------------+------------
 Rohan            | HR
(1 row)

Example 7: the missing WHERE, and how to check first

Without WHERE, UPDATE and DELETE touch every row. Run the statement inside a transaction, check the row count, and ROLLBACK when it is wrong.

SQL

BEGIN;
UPDATE emp_copy SET salary = 0;
SELECT count(*) AS rows_with_zero_salary FROM emp_copy WHERE salary = 0;
ROLLBACK;
SELECT min(salary) AS min_salary_after_rollback FROM emp_copy;

Output

 rows_with_zero_salary 
-----------------------
                    11
(1 row)

 min_salary_after_rollback 
---------------------------
                     70000
(1 row)

Other safeguards:

Write the WHERE first, run it as a SELECT, then change the verb.
Use RETURNING to see what changed.
In psql, \set AUTOCOMMIT off makes every statement wait for an explicit COMMIT.
Test on a copy: CREATE TEMP TABLE t AS SELECT * FROM real_table. A temp table disappears when the session ends.

Example 8: affected row counts

The server reports the number of rows affected as a command tag: INSERT 0 n, UPDATE n, DELETE n. Clients expose it (cmd.rowcount in psycopg, ExecuteNonQuery in ADO.NET). An UPDATE that matches no row reports UPDATE 0 and is not an error.

The same count is available in SQL with a CTE and RETURNING:

SQL

WITH changed AS (
    UPDATE emp_copy SET salary = salary + 1000 WHERE dept_id = 1 RETURNING id
)
SELECT count(*) AS rows_updated FROM changed;

Output

 rows_updated 
--------------
            5
(1 row)

SQL

WITH changed AS (
    UPDATE emp_copy SET salary = salary + 1000 WHERE dept_id = 99 RETURNING id
)
SELECT count(*) AS rows_updated FROM changed;

Output

 rows_updated 
--------------
            0
(1 row)

Example 9: DELETE vs TRUNCATE vs DROP

	`DELETE`	`TRUNCATE`	`DROP TABLE`
Removes	rows matching `WHERE`	all rows	rows and the table definition
`WHERE` allowed	yes	no	no
Speed on large tables	slow, row by row, dead rows left for vacuum	fast, discards the table's files	fast
Fires row triggers	yes	no (statement triggers only)	no
Rollback in PostgreSQL	yes	yes	yes
Resets identity/serial	no	only with `RESTART IDENTITY`	table is gone
Foreign keys	checked per row	refused if referenced, unless `CASCADE`	refused if referenced, unless `CASCADE`
Type	DML	DDL	DDL

Rollback of TRUNCATE and DROP works in PostgreSQL and SQL Server. In MySQL and Oracle, DDL commits implicitly.

SQL

BEGIN;
DELETE FROM audit_log WHERE action IN ('a', 'b');
SELECT count(*) AS after_delete FROM audit_log;
TRUNCATE audit_log RESTART IDENTITY;
SELECT count(*) AS after_truncate FROM audit_log;
INSERT INTO audit_log (action) VALUES ('fresh') RETURNING id;
ROLLBACK;
SELECT count(*) AS after_rollback FROM audit_log;

Output

 after_delete 
--------------
            3
(1 row)

 after_truncate 
----------------
              0
(1 row)

 id 
----
  1
(1 row)

 after_rollback 
----------------
              5
(1 row)

TRUNCATE is refused when another table has a foreign key to it:

SQL

TRUNCATE departments;

Output

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "employees" references "departments".
HINT:  Truncate table "employees" at the same time, or use TRUNCATE ... CASCADE.

Example 10: upsert with ON CONFLICT DO NOTHING

INSERT ... ON CONFLICT handles a violation of a unique or primary key without raising an error. DO NOTHING skips the conflicting row.

SQL

INSERT INTO dept_copy (id, name) VALUES (1, 'Duplicate'), (7, 'Support')
ON CONFLICT (id) DO NOTHING
RETURNING id, name;

Output

 id |  name   
----+---------
  7 | Support
(1 row)

Only the new row (7) is returned. Running the statement again inserts nothing, so it is safe to repeat.

Example 11: ON CONFLICT DO UPDATE

DO UPDATE changes the existing row instead. EXCLUDED is the row that was proposed for insertion.

SQL

INSERT INTO dept_copy (id, name) VALUES (1, 'Platform'), (8, 'Finance')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
RETURNING id, name, (xmax = 0) AS inserted;

Output

 id |   name   | inserted 
----+----------+----------
  1 | Platform | f
  8 | Finance  | t
(2 rows)

xmax = 0 is true for a row that was just inserted and false for one that was updated. It is a PostgreSQL implementation detail, useful for demos, not for production logic.

The conflict target must match a unique index or constraint. The statement is idempotent: running it twice leaves the same table.

MySQL writes this as INSERT ... ON DUPLICATE KEY UPDATE. SQLite also supports ON CONFLICT. Oracle and SQL Server use MERGE.

Example 12: MERGE (PostgreSQL 15 and later)

MERGE compares a source to a target and applies INSERT, UPDATE, or DELETE per row. It is standard SQL. PostgreSQL 17 adds RETURNING to MERGE; version 16 does not.

SQL

MERGE INTO dept_copy t
USING (VALUES (2, 'Sales & Growth'), (9, 'Research')) AS s(id, name)
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

SELECT * FROM dept_copy ORDER BY id;

Output

 id |      name      
----+----------------
  1 | Platform
  2 | Sales & Growth
  3 | Marketing
  4 | HR
  5 | Legal
  6 | Sales Leads
  7 | Support
  8 | Finance
  9 | Research
(9 rows)

ON CONFLICT relies on a unique index and is safe under concurrent inserts. MERGE does not need a unique index, but under concurrency two sessions can both take the NOT MATCHED branch; with a unique constraint one of them then fails with a unique violation. Use ON CONFLICT for a plain upsert.

Example 13: clean up

Drop the copies. DROP TABLE IF EXISTS name avoids an error when the table is absent.

SQL

DROP TABLE emp_copy, dept_copy, audit_log;
SELECT count(*) AS original_employees FROM employees;

Output

 original_employees 
--------------------
                 12
(1 row)

Interview points

Question	Answer
`DELETE` vs `TRUNCATE` vs `DROP`	`DELETE` removes chosen rows and can use `WHERE` and fire row triggers. `TRUNCATE` empties the table quickly and takes an exclusive lock. `DROP` removes the table itself.
Can `TRUNCATE` be rolled back?	In PostgreSQL and SQL Server yes. In MySQL and Oracle no, DDL commits.
Why is `DELETE` of all rows slower than `TRUNCATE`?	It processes each row, writes WAL per row, and leaves dead rows for `VACUUM`.
What makes an upsert idempotent?	A unique key plus `ON CONFLICT`: repeating the statement produces the same final table. `DO NOTHING` keeps the old row, `DO UPDATE` overwrites it.
`ON CONFLICT` vs `MERGE`	`ON CONFLICT` needs a unique index and is concurrency-safe. `MERGE` is standard, more general (can `DELETE`), and needs care under concurrency.
How do you avoid an accidental unbounded update?	Transaction plus row-count check, `RETURNING`, run the `WHERE` as a `SELECT` first.

Summary

List columns in INSERT; use RETURNING to get generated values and changed rows back.
UPDATE ... FROM and DELETE ... USING change rows using a join.
Test destructive statements in a transaction and check the row count before COMMIT.
DELETE removes rows, TRUNCATE empties a table fast, DROP removes the table.
ON CONFLICT gives an idempotent upsert; MERGE (PG 15+) covers multi-branch cases.