SQL INSERT, UPDATE and DELETE

Why it matters

A wrong SELECT shows a wrong answer; a wrong UPDATE or DELETE destroys data. One missing condition can cancel every order on a shared test environment, and most clients commit each statement as soon as it succeeds, so there is no undo.

Used with discipline, the same statements make testing faster and deeper: a seed script gives every run the same starting data, an UPDATE creates an expired subscription in seconds, and a statement that breaks a rule on purpose shows whether the database protects the data on its own.

INSERT: one row or many

INSERT adds new rows: the column list, then the values in the same order. A column you leave out gets its DEFAULT, or NULL if it has none; a NOT NULL column without a default makes the statement fail. When the database generates the key, leave that column out and find the new row by a value you chose, such as the email.

A multi-row INSERT puts several lists of values after one VALUES keyword; PostgreSQL, MySQL, SQLite and SQL Server all accept it. The statement is atomic: if one row breaks a rule, none is inserted (in MySQL, with the default InnoDB engine).

• Name the columns: a statement that relies on the table's column order breaks, or fills the wrong columns, when the table changes.
• Text and dates go in single quotes, numbers and NULL do not; a quote inside text is doubled: 'O''Brien'.
• Insert parents before children, because each foreign key checks that the parent exists (SQLite enforces foreign keys only after PRAGMA foreign_keys = ON).

-- Single-row INSERT: the column list, then the values in the same order
INSERT INTO customers (customer_id, email, full_name, created_at)
VALUES (9001, 'qa.sara@example.com', 'QA Sara Ahmed', '2026-09-01 09:00:00');
-- 1 row inserted

-- Multi-row INSERT: one statement, one list of values per row
INSERT INTO orders (order_id, customer_id, status, total_amount, created_at)
VALUES (9101, 9001, 'PENDING',  45.00, '2026-09-01 10:00:00'),
       (9102, 9001, 'PENDING', 120.00, '2026-09-01 10:05:00'),
       (9103, 9001, 'PAID',     80.00, '2026-09-02 09:30:00');
-- 3 rows inserted, or none: one bad row rejects the whole statement

-- A child row: order 9102 must already exist
INSERT INTO order_items (order_item_id, order_id, product_sku, quantity, unit_price)
VALUES (9201, 9102, 'SKU-100', 2, 60.00);

Try it: SQL INSERT Generator Try it: SQL VALUES Generator

UPDATE and DELETE: count the rows first

UPDATE changes columns of existing rows and DELETE removes whole rows. The WHERE clause decides which rows are hit; without it, every row is. A WHERE that is slightly wrong is worse, because it looks right: a LIKE pattern that matches more than you meant, = NULL that matches nothing, an OR that escapes its AND.

So count first. Run the exact WHERE in a SELECT that lists the rows and in SELECT COUNT(*), and compare the count with what the task leads you to expect. Then replace only the lines above WHERE to make the UPDATE or DELETE, and compare the rows-affected number the client reports with your count. If they differ, stop.

• psql prints UPDATE 2, SQL Server (2 rows affected). For an UPDATE, MySQL reports rows matched and rows changed, and by default counts as affected only the rows whose value really changed, so compare rows matched with your count.
• Before deleting a parent row, count its children: a foreign key rejects the DELETE or, with ON DELETE CASCADE, deletes them too, and the reported count covers only the table you named.

-- 1. The WHERE you intend, as a SELECT: look at the rows, then count them
SELECT order_id, status
FROM orders
WHERE customer_id = 9001 AND status = 'PENDING';
-- 9101  PENDING
-- 9102  PENDING

SELECT COUNT(*)
FROM orders
WHERE customer_id = 9001 AND status = 'PENDING';
-- 2, as expected: the test customer has two pending orders

-- 2. The same WHERE, only the lines above it changed
UPDATE orders
SET status = 'CANCELLED'
WHERE customer_id = 9001 AND status = 'PENDING';
-- rows affected: 2, equal to the count

-- 3. DELETE: count the children before removing the parent
SELECT COUNT(*) FROM order_items WHERE order_id = 9102;   -- 1
DELETE FROM orders WHERE order_id = 9102;
-- rejected by the foreign key from order_items; with ON DELETE CASCADE
-- it would succeed, report 1 row and remove the item line as well

Try it: SQL Query Validator Try it: SQL IN Clause Generator

Rehearse inside a transaction: BEGIN … ROLLBACK

Most clients start in autocommit mode (check the setting in yours): each statement is committed as soon as it succeeds. BEGIN opens a transaction instead: a group of statements that the database keeps or undoes as one unit. Your session sees the changes that follow, but other sessions, including the application, do not, until COMMIT makes them permanent; ROLLBACK discards them all. This holds under the default isolation level, the setting that decides what other sessions can see of a transaction that is not yet committed; the guide on testing transactions and database migrations explains the levels. SQL Server writes BEGIN TRANSACTION, and MySQL also accepts START TRANSACTION.

The routine: count with SELECT, BEGIN, run the change, compare the rows affected with the count, check the result with a SELECT inside the transaction, then ROLLBACK if anything differs or you were only rehearsing. COMMIT only when every number matches and the change is meant to stay.

Do this only on a test database: your own disposable one, or a shared environment whose owner, usually the QA lead or the team that runs it, has agreed. Changes to production data go through the team's change process, not a tester's SQL client.

• The application cannot see uncommitted rows: to test a screen with the new data you must COMMIT, and plan how to restore it.
• An open transaction locks the rows it changed and makes other writers wait: finish promptly.
• In PostgreSQL an error aborts the transaction; every later statement fails until ROLLBACK.
• ROLLBACK does not undo everything: PostgreSQL, MySQL and SQL Server do not hand back generated key values already used, and in MySQL TRUNCATE or ALTER TABLE commits the open transaction implicitly.

Seeding test data and provoking constraint violations

Seeding loads known data before a test so that it starts from a state you chose. Keep the INSERT statements in a seed script next to the tests, and mark every row with a QA- prefix, an example.com email or an agreed key range, so that the script can first delete its own rows, children before parents.

SQL goes around the application: a row written by hand skips its validation, audit log and caches. That reaches states quickly, such as an order created 31 days ago, but can also produce data the product could never create, so note in the test case which data came from SQL.

DML also tests the database's own rules, its constraints: NOT NULL, the primary key, UNIQUE, foreign keys and CHECK, a condition every row must meet, such as CHECK (quantity > 0). Write a statement that breaks exactly one rule and predict the result: rejected with a constraint error, and no data changed. Constraints guard UPDATE and DELETE as well as INSERT. End each attempt with ROLLBACK, so that if the rule is missing the bad data does not stay: a statement that succeeds is the finding.

-- One broken rule per transaction, on your own test database.
-- If a rule is missing, ROLLBACK still removes the bad data.

BEGIN;
UPDATE order_items SET quantity = 0 WHERE order_item_id = 9201;
-- expected: rejected by CHECK (quantity > 0); quantity stays 2
ROLLBACK;

BEGIN;
DELETE FROM customers WHERE customer_id = 9001;
-- expected: rejected by the foreign key from orders (9101 to 9103)
ROLLBACK;

BEGIN;
INSERT INTO customers (customer_id, email, full_name, created_at)
VALUES (9002, 'qa.sara@example.com', 'QA Copy', '2026-09-01 09:00:00');
-- expected: rejected by UNIQUE (email); still one row with this email
ROLLBACK;

Try it: SQL Dataset Generator Try it: CSV to SQL

Worked example: rehearsing a data-fix script

A ticket contains a data-fix script that a developer will run in production: cancel abandoned orders, meaning orders still PENDING from before 1 August with no captured payment. The ticket says it cancels three: 1201, 1202 and 1205. Staging holds a recent masked copy of production, and its owner agrees that you rehearse the script there, provided staging looks the same afterwards.

Counting first with the script's own WHERE returns 4. Listing those rows with their payments shows why: order 1188 is PENDING but has a CAPTURED payment, and the script has no condition on payments. Nothing has changed yet, so you stop and report.

• Finding 1: the script would cancel a paid order. The developer adds NOT EXISTS (SELECT 1 FROM payments …), a subquery that leaves out orders with a captured payment, and the count becomes 3. NOT EXISTS, not NOT IN, because NOT IN matches nothing once its subquery returns a NULL.
• Finding 2: order 1188 is paid but still PENDING. Report it separately: it points to a likely defect in the payment flow.
• Rehearsal: 3 rows affected, equal to the count; inside the transaction 1201, 1202 and 1205 are CANCELLED and the control row 1188 is still PENDING; after ROLLBACK all four are PENDING again.
• The queries, counts and final SELECT go into the ticket: the production run repeats the count first and goes ahead only if it still matches.

-- The developer's data-fix script, to run in production later:
--   UPDATE orders SET status = 'CANCELLED'
--   WHERE status = 'PENDING' AND created_at < '2026-08-01';
-- Ticket: "cancels 3 abandoned orders: 1201, 1202, 1205"

-- 1. Count first with the script's own WHERE
SELECT COUNT(*) FROM orders
WHERE status = 'PENDING' AND created_at < '2026-08-01';
-- 4, the ticket says 3: stop and list the rows with their payments
SELECT o.order_id, p.status AS payment
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.status = 'PENDING' AND o.created_at < '2026-08-01'
ORDER BY o.order_id;
-- 1188  CAPTURED    <- paid, yet still PENDING
-- 1201  NULL
-- 1202  NULL
-- 1205  NULL

-- 2. The developer's fixed WHERE: count again
SELECT COUNT(*) FROM orders
WHERE status = 'PENDING' AND created_at < '2026-08-01'
  AND NOT EXISTS (SELECT 1 FROM payments p
                  WHERE p.order_id = orders.order_id AND p.status = 'CAPTURED');
-- 3

-- 3. Rehearse on staging with the owner's agreement, then undo
BEGIN;
UPDATE orders SET status = 'CANCELLED'
WHERE status = 'PENDING' AND created_at < '2026-08-01'
  AND NOT EXISTS (SELECT 1 FROM payments p
                  WHERE p.order_id = orders.order_id AND p.status = 'CAPTURED');
-- rows affected: 3, equal to the count

SELECT order_id, status FROM orders
WHERE order_id IN (1188, 1201, 1202, 1205);
-- 1188 PENDING, 1201 CANCELLED, 1202 CANCELLED, 1205 CANCELLED

ROLLBACK;
-- the same SELECT now shows all four orders PENDING again

Frequently asked questions

References

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development GroupWhat a transaction is: several steps applied all-or-nothing with BEGIN, COMMIT and ROLLBACK, invisible to others until committed.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.