INSERT creates rows, UPDATE changes selected rows, and DELETE removes selected rows. The safety rule for the last two is simple: check the rows with a matching SELECT before you run the write, and use a transaction when you need a chance to validate or undo related changes.
What INSERT, UPDATE, and DELETE do
| Statement | Effect | How it selects data |
|---|---|---|
INSERT |
Creates one or more rows. | Supplied values or the result of a query. |
UPDATE |
Changes values in existing rows. | A WHERE condition identifies rows; SET identifies columns to change. |
DELETE |
Removes existing rows. | A WHERE condition identifies rows to remove. |
These are SQL data-manipulation statements. Exact syntax and options vary by database engine.
Basic syntax
INSERT INTO customers (name, email)
VALUES ('Ada Lovelace', 'ada@example.com');
UPDATE customers
SET email = 'ada@new.example'
WHERE customer_id = 42;
DELETE FROM customers
WHERE customer_id = 42;The examples use literal values to make the statement shapes easy to see. Applications should use parameterized statements rather than building SQL by concatenating user input.
INSERT: create rows
The column list says which values the statement supplies and the order in which they appear in VALUES. Columns omitted from that list receive their defined default; if no default exists, they receive NULL when allowed. PostgreSQL also supports inserting rows from a query, handling conflicts with ON CONFLICT, and returning inserted data with RETURNING. See the PostgreSQL INSERT reference for its exact syntax.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
UPDATE: change selected rows
SET names the columns to change. Columns not named there retain their existing values. The WHERE clause determines which rows are changed; without a condition, the statement applies to every row. PostgreSQL documents its behavior and syntax in the UPDATE reference.
DELETE: remove selected rows
The WHERE clause selects rows for removal. If it is omitted, the statement removes every row in the table. MySQL classifies DELETE alongside INSERT and UPDATE as a data-manipulation statement; its engine-specific options are described in the MySQL DELETE reference.
How to avoid changing the wrong rows
- Preview the target. Run a
SELECTusing the exact sameWHEREcondition you plan to use in theUPDATEorDELETE. - Check keys and count. Confirm the returned identifiers and number of rows match what you intend to affect.
- Use a constrained identifier. Prefer a primary key or another suitably constrained identifier over a broad condition when the task is to change a particular row.
- Limit the update. Put only the necessary columns in
SETso unrelated values remain unchanged. - Execute and verify. Run the write only after the preview matches your expectation, then inspect the affected rows or returned values using the features available in your database.
For example, before changing a customer’s email, inspect the same key:
SELECT customer_id, email
FROM customers
WHERE customer_id = 42;If that query returns unexpected rows—or more rows than intended—do not run the write until the predicate is corrected.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteUse a transaction for related changes
A transaction groups multiple statements into an all-or-nothing operation. That matters when a task has several dependent writes: either commit the complete set after validation or roll it back if something is wrong. PostgreSQL explains transaction behavior in its transaction tutorial.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
-- Inspect the results before deciding.
COMMIT;If validation fails before the transaction commits, run ROLLBACK instead of COMMIT. In PostgreSQL, changes made within an open transaction are not visible to other transactions until completion, when they become visible together.
Savepoints for partial recovery
A savepoint marks a point within a transaction. If a later step needs to be discarded while earlier work should remain, roll back to the savepoint, then continue or commit the remaining work:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
SAVEPOINT after_debit;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
-- If the later step is wrong:
ROLLBACK TO SAVEPOINT after_debit;
-- Continue with corrected work, or undo everything:
ROLLBACK;Transaction syntax and details differ among database systems. In particular, a committed change is not generally undone by issuing ROLLBACK afterward; recovery then depends on backups, logs, or an application-specific corrective operation.
Best Value
Why rollback behavior depends on the database
A single statement and a multi-statement transaction are not the same thing. The defaults determine whether a write is committed as soon as it completes or remains part of an explicit transaction.
| Database | Default or behavior documented | Transaction controls and caveat |
|---|---|---|
| PostgreSQL | Each standalone statement is implicitly wrapped in a transaction. | Use BEGIN, COMMIT, and ROLLBACK to control a multi-statement transaction; SAVEPOINT supports partial rollback. See the PostgreSQL transaction tutorial. |
| MySQL 8.4 | Autocommit is enabled by default, so a statement outside an explicit transaction is committed independently. | Use START TRANSACTION, COMMIT, and ROLLBACK for a multi-statement unit. See the MySQL transaction control reference. |
| SQLite | Database access automatically starts transactions; INSERT, UPDATE, and DELETE are write statements. |
SQLite permits only one simultaneous write transaction. See the SQLite transaction reference. |
Do not assume that a client tool’s displayed workflow overrides the database’s transaction behavior. Check the documentation for the engine and version you actually use.
Features and permissions are engine-specific
PostgreSQL supports RETURNING for INSERT and UPDATE, letting a statement return affected data, and offers ON CONFLICT for insert conflicts. It also supports PostgreSQL-specific UPDATE ... FROM behavior. These are not universal SQL guarantees; check the relevant engine’s documentation before relying on them.
Likewise, required privileges, locking, concurrency behavior, and conflict handling depend on the database and the statement form. For engine-specific details, consult the official PostgreSQL INSERT, PostgreSQL UPDATE, MySQL DELETE, and SQLite transaction references.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
