Modifying Data

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

This page covers the standard SQL statements used to modify the data stored in a table: INSERT, UPDATE, DELETE, MERGE, and TRUNCATE. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines these statements.

INSERT

The INSERT statement adds one or more new rows to a table.

INSERT INTO table_name (column_1, column_2, ..., column_n)
VALUES (value_1, value_2, ..., value_n)

Parameters/options:

  • table_name — the table to insert rows into.

  • (column_1, column_2, …​, column_n) — the optional, explicit list of columns being populated. When omitted, values must be supplied for every column of the table, in the table’s declared column order.

  • VALUES (…​) — the literal or parameterized values to insert, in the same order as the column list.

Single-row insert

Inserts exactly one row, supplying a value for each named column.

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (101, 'Ana', 'Torres', 'Engineering', 62000.00)

Multi-row insert

A single INSERT statement can supply several value lists, separated by commas, to insert multiple rows in one statement.

INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES
    (102, 'Bruno', 'Silva', 'Sales', 48000.00),
    (103, 'Carla', 'Nunes', 'Marketing', 51000.00),
    (104, 'Diego', 'Reyes', 'Engineering', 59500.00)

INSERT …​ SELECT

Instead of literal VALUES, the rows to insert can come from the result of a SELECT query, letting you copy or transform rows from other tables.

INSERT INTO employees_archive (employee_id, first_name, last_name, department, salary)
SELECT employee_id, first_name, last_name, department, salary
FROM employees
WHERE department = 'Sales'

Parameters/options:

  • The SELECT query’s result columns must match the target column list in number and be assignable to their corresponding data types.

  • Any valid SELECT statement may be used, including joins, WHERE filters, and aggregations.

UPDATE

The UPDATE statement modifies the values of existing rows in a table.

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ...
WHERE condition

Parameters/options:

  • table_name — the table whose rows are updated.

  • SET column_1 = value_1, …​ — one or more column assignments to apply to each matched row. Values may be literals, expressions, or subqueries.

  • WHERE condition — a predicate that selects which rows are updated. When omitted, every row in the table is updated.

UPDATE employees
SET salary = salary * 1.05, department = 'Engineering'
WHERE department = 'Sales' AND salary < 50000.00

DELETE

The DELETE statement removes existing rows from a table.

DELETE FROM table_name
WHERE condition

Parameters/options:

  • table_name — the table to delete rows from.

  • WHERE condition — a predicate that selects which rows are deleted. When omitted, every row in the table is deleted, one row at a time, while still firing any row-level triggers.

DELETE FROM employees
WHERE department = 'Marketing' AND salary < 45000.00

Standard SQL has no clause for limiting how many rows a single DELETE removes — unlike SELECT’s `OFFSET/ FETCH FIRST …​ ROWS ONLY (see Querying Data (SELECT)), a DELETE statement always removes every row matching its WHERE condition in one operation. "Batch deleting" (removing rows a limited number at a time, often to reduce lock contention on large tables) is achieved through vendor-specific extensions — for example a non-standard LIMIT clause on DELETE, or a row-count cap such as SET ROWCOUNT — or by an application issuing repeated standard DELETE statements, each scoped with WHERE to a different subset of rows (for example a range of key values). Neither approach is standard SQL, so neither is documented further here.

MERGE

The MERGE statement (introduced in SQL:2003) combines insert, update, and delete operations into a single statement by comparing rows from a source with rows in a target table.

MERGE INTO target_table AS t
USING source_table AS s
ON t.match_column = s.match_column
WHEN MATCHED THEN
    UPDATE SET t.column_1 = s.column_1, t.column_2 = s.column_2
WHEN NOT MATCHED THEN
    INSERT (match_column, column_1, column_2)
    VALUES (s.match_column, s.column_1, s.column_2)

Parameters/options:

  • target_table — the table being modified.

  • source_table — the table, view, or subquery supplying candidate rows.

  • ON condition — the join condition used to match source rows against target rows.

  • WHEN MATCHED THEN — the action to take for rows that satisfy the ON condition.

  • WHEN NOT MATCHED THEN — the action to take for source rows that have no matching target row.

WHEN MATCHED THEN UPDATE

When a source row matches an existing target row, UPDATE sets the given target columns from the source row.

MERGE INTO employees AS t
USING employee_updates AS s
ON t.employee_id = s.employee_id
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary, t.department = s.department

WHEN MATCHED THEN DELETE

When a source row matches an existing target row, DELETE removes the matched target row instead of updating it.

MERGE INTO employees AS t
USING employee_terminations AS s
ON t.employee_id = s.employee_id
WHEN MATCHED THEN
    DELETE

WHEN NOT MATCHED THEN INSERT

When a source row has no matching target row, INSERT adds it to the target table as a new row.

MERGE INTO employees AS t
USING employee_updates AS s
ON t.employee_id = s.employee_id
WHEN MATCHED THEN
    UPDATE SET t.salary = s.salary, t.department = s.department
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, department, salary)
    VALUES (s.employee_id, s.first_name, s.last_name, s.department, s.salary)

TRUNCATE

TRUNCATE TABLE removes all rows from a table. Strictly speaking it is a Data Definition Language (DDL) statement rather than a data-modification (DML) one — it cannot be selectively filtered with a WHERE clause and, depending on the standard’s isolation rules, may not be undone by a transaction rollback in the same way DELETE can — but it is commonly grouped alongside DELETE because both are used to remove data from a table.

TRUNCATE TABLE table_name

Parameters/options:

  • TABLE — the mandatory keyword preceding the table name in the standard syntax.

  • table_name — the table whose rows are all removed. Unlike DELETE, no WHERE clause is permitted — TRUNCATE TABLE always removes every row.

TRUNCATE TABLE employees_archive