Changing Data

Data manipulation language

SQL statements fall into groups. The statements that read and change the rows in tables form the data manipulation language (DML). The statements that create and change the tables themselves (CREATE, ALTER, DROP) are the data definition language, covered in Designing Tables.

Each example on this page starts with a fresh copy of the sample shop, so changes do not carry over from one example to the next. Statements that change data print nothing, so each example ends with a SELECT that shows the effect.

INSERT: adding rows

The INSERT statement adds rows to a table. You name the table, list the columns you are providing, and give the values in the same order:

INSERT INTO customers (first_name, last_name, email, city, country, signup_date)
VALUES ('Jan', 'Smit', 'jan.smit@example.com', 'Leiden', 'NL', '2026-10-01');

SELECT customer_id, first_name, last_name, city
FROM customers
WHERE email = 'jan.smit@example.com';

customer_id  first_name  last_name  city
-----------  ----------  ---------  ------
9            Jan         Smit       Leiden

The statement does not mention customer_id. Because it is declared INTEGER PRIMARY KEY, SQLite assigns one more than the highest existing id, 9. Other systems mark such a column explicitly: GENERATED ALWAYS AS IDENTITY (standard SQL, PostgreSQL, Oracle) or SERIAL (older PostgreSQL), IDENTITY(1,1) (SQL Server), AUTO_INCREMENT (MySQL). A column that is left out gets its DEFAULT value, or NULL if it has none: inserting a product without stock gives it a stock of 0, the default declared in shop.sql.

Several rows at once

One INSERT can add several rows, separated by commas. This is faster than separate statements and is supported by all major databases (Oracle only since version 23ai; SQL Server allows up to 1,000 rows in one INSERT … VALUES statement):

INSERT INTO products (name, category, price)
VALUES ('Mouse pad',             'Accessories', 8),
       ('DisplayPort cable 2 m', 'Cables',      14),
       ('Docking station',       'Accessories', 159);

SELECT product_id, name, price, stock
FROM products
WHERE product_id > 10
ORDER BY product_id;

product_id  name                   price  stock
----------  ---------------------  -----  -----
11          Mouse pad              8      0
12          DisplayPort cable 2 m  14     0
13          Docking station        159    0

All three products got a stock of 0 from the column's DEFAULT.

Always list the columns

shop.sql itself uses the short form INSERT INTO customers VALUES (...), without a column list. That is fine for a one-off setup script, but in application code it is fragile: the values are matched to the columns by position, so the statement breaks (or, worse, puts values in the wrong columns) as soon as someone adds or reorders a column. With an explicit column list the statement keeps working, and the reader can see which value goes where.

INSERT … SELECT

Instead of VALUES, an INSERT can take the rows from a query. This copies data between tables in one statement, without the data ever leaving the database. A mailing list of all Dutch customers:

CREATE TABLE newsletter (
  email       TEXT PRIMARY KEY,
  first_name  TEXT NOT NULL
);

INSERT INTO newsletter (email, first_name)
SELECT email, first_name
FROM customers
WHERE country = 'NL';

SELECT email, first_name FROM newsletter ORDER BY email;

email                      first_name
-------------------------  ----------
kwame.mensah@example.com   Kwame
mei.chen@example.com       Mei
priya.patel@example.com    Priya
sanne.devries@example.com  Sanne
tom.jansen@example.com     Tom

When a change breaks a rule

The tables of the shop declare rules, called constraints: email addresses must be unique, prices must be positive, an order must belong to an existing customer (see Designing Tables). The database checks them on every change and refuses a change that would break one. The error message names the rule. A second customer with Mei's email address:

INSERT INTO customers (first_name, last_name, email, country, signup_date)
VALUES ('Mei', 'Chen', 'mei.chen@example.com', 'NL', '2026-10-01');

Error: UNIQUE constraint failed: customers.email

A product with a price of zero:

INSERT INTO products (name, category, price)
VALUES ('Sticker pack', 'Accessories', 0);

Error: CHECK constraint failed: price > 0

An order for a customer that does not exist (there is no customer 99):

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (111, 99, '2026-10-01', 'pending');

Error: FOREIGN KEY constraint failed

And a required column that was left out (signup_date is NOT NULL and has no default):

INSERT INTO customers (first_name, last_name, email, country)
VALUES ('Jan', 'Smit', 'jan.smit@example.com', 'NL');

Error: NOT NULL constraint failed: customers.signup_date

In each case the statement is undone as a whole and the table is unchanged. That includes multi-row statements: if one row of a 500-row INSERT breaks a rule, none of the 500 rows are inserted. These errors are not a nuisance; they are the database protecting the data from a bug in the application. SQLite only enforces foreign keys after PRAGMA foreign_keys = ON (the sample database switches it on); PostgreSQL, MySQL (InnoDB) and SQL Server enforce them by default.

UPDATE: changing rows

The UPDATE statement changes values in existing rows. SET says which columns get which new values; WHERE says which rows. The keyboard gets cheaper:

UPDATE products
SET price = 79
WHERE product_id = 4;

SELECT product_id, name, price FROM products WHERE product_id = 4;

product_id  name                 price
----------  -------------------  -----
4           Mechanical keyboard  79

Filter on the primary key when you mean one specific row; a name can be misspelled or shared by two rows. The new value can be computed from the old one. When two keyboards are sold, decrease the stock rather than overwriting it with a number calculated elsewhere — the database does the arithmetic on the current value, which is safer when several sales happen at the same moment:

UPDATE products
SET stock = stock - 2
WHERE product_id = 4;

SELECT product_id, name, stock FROM products WHERE product_id = 4;

product_id  name                 stock
----------  -------------------  -----
4           Mechanical keyboard  10

Several columns change in one statement by separating them with commas (SET price = 79, stock = 20), and the WHERE can match many rows on purpose: UPDATE orders SET status = 'delivered' WHERE status = 'shipped'. Constraints apply to updates too. Selling more monitors than are in stock (7):

UPDATE products
SET stock = stock - 10
WHERE product_id = 5;

Error: CHECK constraint failed: stock >= 0

The danger of a missing WHERE

WHERE is optional in UPDATE and DELETE. Without it, the statement applies to every row in the table, and the database does not ask whether you are sure:

UPDATE products
SET price = 10;          -- meant: WHERE product_id = 2

SELECT product_id, name, price FROM products ORDER BY product_id LIMIT 4;

product_id  name                 price
----------  -------------------  -----
1           USB-C cable 2 m      10
2           HDMI cable 3 m       10
3           Wireless mouse       10
4           Mechanical keyboard  10

A routine for safe changes

1. Preview with SELECT. Write the WHERE first as a SELECT with the same condition, and check the rows and their count.
2. Start a transaction. BEGIN before the change; nothing is permanent until COMMIT.
3. Check the number of changed rows. Every database tool reports it ("10 rows affected"); in SQLite, changes() returns it. If it is not the number the preview showed, roll back.
4. Commit or roll back. COMMIT makes the change permanent; ROLLBACK undoes everything since BEGIN.

BEGIN;

UPDATE products
SET price = 10;               -- oops: no WHERE

SELECT changes() AS rows_changed;   -- expected 1, got 10

ROLLBACK;

SELECT product_id, name, price FROM products ORDER BY product_id LIMIT 4;

rows_changed
------------
10

product_id  name                 price
----------  -------------------  -----
1           USB-C cable 2 m      9
2           HDMI cable 3 m       12
3           Wireless mouse       25
4           Mechanical keyboard  89

The rollback restored every price. Transactions, and what they guarantee, are explained in Transactions and ACID. Some tools add a safety net of their own: the MySQL command-line client has a --safe-updates option that refuses UPDATE and DELETE without a key in the WHERE, and many graphical tools warn before such statements. And give applications a database account that can only do what they need (least privilege): a reporting tool that can only SELECT cannot delete anything by accident.

DELETE: removing rows

The DELETE statement removes whole rows; it has no column list, because it never removes part of a row (to clear one value, UPDATE it to NULL). Like UPDATE, it applies to the rows that match WHERE, and to all rows without one. Removing the HDMI cable from order 109:

DELETE FROM order_items
WHERE order_id = 109 AND product_id = 2;

SELECT order_id, product_id, quantity FROM order_items WHERE order_id = 109;

order_id  product_id  quantity
--------  ----------  --------
109       3           1

order_items has a two-column primary key (order_id, product_id), so both columns are needed to identify one line. WHERE order_id = 109 alone would remove the whole order's lines.

DELETE and foreign keys

A row that other rows refer to cannot simply disappear: the orders would point to a customer that no longer exists. Sanne has two orders, so deleting her fails:

DELETE FROM customers WHERE customer_id = 1;

Error: FOREIGN KEY constraint failed

This is referential integrity at work. To really remove her, you would delete the rows that depend on her first (order lines, then orders, then the customer), or declare the foreign key with ON DELETE CASCADE so the database deletes the dependent rows itself. For business records such as orders, neither is usually what you want, which is one reason for soft deletes. A customer without orders, such as Ana, can be deleted without any problem:

DELETE FROM customers WHERE customer_id = 8;

SELECT COUNT(*) AS customers FROM customers;

customers
---------
7

Upsert: insert or update

A common task is "add this row, or update it if it is already there", for example when a delivery arrives and its products may or may not be in the catalog yet. Checking first with a SELECT and then choosing INSERT or UPDATE takes two round trips and can go wrong when two sessions do it at the same moment. An upsert (update + insert) does it in one atomic statement. SQLite (since 3.24) and PostgreSQL (since 9.5) use ON CONFLICT:

INSERT INTO products (product_id, name, category, price, stock)
VALUES (3,  'Wireless mouse',  'Peripherals', 25, 20),
       (11, 'Mouse pad',       'Accessories', 8,  60)
ON CONFLICT (product_id) DO UPDATE
SET stock = stock + excluded.stock;

SELECT product_id, name, stock FROM products WHERE product_id IN (3, 11) ORDER BY product_id;

product_id  name            stock
----------  --------------  -----
3           Wireless mouse  65
11          Mouse pad       60

Product 3 already existed, so its stock went from 45 to 65; product 11 did not, so it was inserted. excluded refers to the row that would have been inserted. The conflict target (product_id) must be a primary key or have a unique constraint. ON CONFLICT DO NOTHING simply skips rows that already exist. The other big systems write it differently (not run here):

-- MySQL / MariaDB
INSERT INTO products (product_id, name, category, price, stock)
VALUES (3, 'Wireless mouse', 'Peripherals', 25, 20)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);
-- MySQL 8.0.19+ prefers a row alias: ... VALUES (...) AS new
--   ON DUPLICATE KEY UPDATE stock = stock + new.stock;

-- SQL Server (PostgreSQL 15+ accepts the same; Oracle has MERGE with slightly different syntax)
MERGE INTO products AS target
USING (VALUES (3, 'Wireless mouse', 'Peripherals', 25, 20))
      AS source (product_id, name, category, price, stock)
ON target.product_id = source.product_id
WHEN MATCHED THEN
    UPDATE SET stock = target.stock + source.stock
WHEN NOT MATCHED THEN
    INSERT (product_id, name, category, price, stock)
    VALUES (source.product_id, source.name, source.category, source.price, source.stock);

MERGE is the SQL standard's statement for this and can also delete; it is powerful but verbose. In SQL Server, two sessions running the same MERGE at once can still collide unless the statement holds the right locks (a HOLDLOCK hint is the usual advice).

RETURNING: seeing what changed

After an INSERT the application often needs the generated key, and after an UPDATE the new value. A RETURNING clause makes a DML statement return the affected rows, like a SELECT, without a second query. SQLite supports it since version 3.35, PostgreSQL for a long time:

INSERT INTO customers (first_name, last_name, email, country, signup_date)
VALUES ('Jan', 'Smit', 'jan.smit@example.com', 'NL', '2026-10-01')
RETURNING customer_id, first_name;

UPDATE products
SET stock = stock - 2
WHERE product_id = 4
RETURNING product_id, name, stock;

customer_id  first_name
-----------  ----------
9            Jan

product_id  name                 stock
----------  -------------------  -----
4           Mechanical keyboard  10

SQL Server has the same idea as an OUTPUT clause (INSERT … OUTPUT inserted.customer_id VALUES …). MySQL has no RETURNING; applications read the generated key through the driver (LAST_INSERT_ID()). MariaDB supports RETURNING for INSERT and DELETE. From code, drivers also expose the last inserted id and the number of affected rows; see Working with Databases from Code.

Removing all rows, and not removing at all

TRUNCATE versus DELETE

To empty a whole table, PostgreSQL, MySQL, SQL Server and Oracle offer TRUNCATE TABLE name. It removes all rows at once instead of one by one, which makes it much faster on large tables and leaves less in the transaction log. It is also blunter:

Oracle, SQL Server and MySQL classify TRUNCATE as data definition language rather than DML. SQLite has no TRUNCATE; a DELETE FROM t without WHERE is internally optimized to empty the table quickly, much like a truncate.

Soft deletes

Many applications never physically delete business records. Instead they mark them as deleted with a column such as deleted_at, a soft delete, and filter those rows out in normal queries:

ALTER TABLE customers ADD COLUMN deleted_at TEXT;

UPDATE customers
SET deleted_at = '2026-10-01'
WHERE customer_id = 1;

SELECT COUNT(*) AS active_customers
FROM customers
WHERE deleted_at IS NULL;

active_customers
----------------
7

Sanne's orders stay valid (the foreign key still points to an existing row), the history is kept for reporting and auditing, and an accidental "delete" can be undone. The costs: every query must remember WHERE deleted_at IS NULL (a view of active customers helps), unique constraints still see the hidden rows, and the table keeps growing. And a soft delete is not erasure: when a customer exercises the right to erasure under the General Data Protection Regulation (GDPR), personal data must really be removed or anonymized (see retention and deletion).