MySQL INSERT, UPDATE, and DELETE

Three statements for changing rows

INSERT creates rows, UPDATE changes values in existing rows, and DELETE removes rows. These statements are part of data manipulation language (DML). Unlike a SELECT query, a write changes stored state, so correctness includes both the values in the statement and the exact records matched. Always list the destination columns in INSERT statements; relying on physical table column order makes code fragile when a schema evolves.

INSERT INTO products (sku, name, price)
VALUES ('LAMP-204', 'Desk lamp', 34.90);

This inserts one row and lets MySQL apply any configured defaults for columns not listed. For multiple known records, a multi-row INSERT reduces round trips and clearly shows each row’s values. Validate data and rely on unique keys and other constraints as a final integrity boundary. If the insert conflicts with a unique key, decide whether to report the conflict or handle it deliberately; do not ignore errors just to make a batch appear successful.

Use conditional UPDATE statements

An UPDATE without a WHERE clause targets every row in the table. Before a broad or important change, run a SELECT with the same predicate and inspect the candidate rows. Then write the WHERE clause using a stable key or a carefully reviewed condition. Check affected-row counts afterward. A zero-row result can mean that the record does not exist, the predicate is wrong, or the value already matched; application behavior should distinguish what the user needs to know.

UPDATE products
SET price = 31.90
WHERE product_id = 42
  AND active = TRUE;

The additional active predicate makes the intended state explicit. For inventory, encode a safety condition in the write itself rather than reading a quantity and assuming no other transaction can change it first. An update such as SET quantity = quantity - 1 WHERE product_id = ? AND quantity > 0 can avoid decrementing below zero, but the application must still check whether exactly one row changed and handle the unavailable-stock case.

DELETE requires an explicit scope

DELETE removes rows that match its WHERE condition, and deleting without a condition removes every row. Preview the target rows with SELECT, consider foreign-key behavior, and understand whether the application needs a soft-delete marker or a true removal. A soft delete is not automatically safer: access-control filters, uniqueness rules, retention requirements, and eventual purge procedures still need design.

SELECT product_id, sku, name
FROM products
WHERE product_id = 42;

DELETE FROM products
WHERE product_id = 42;

The first query provides a preview, but another session could change the data before the delete. When a strict multi-step operation requires a consistent row, use a transaction and appropriate locking or isolation strategy. Keep operational backups and recovery plans for destructive maintenance; a transaction is not a replacement for a backup after committed data is lost.

Transactions and failure handling

With InnoDB, a transaction can group related writes. Commit only after checking required conditions; roll back when a step fails. A transaction should be short because open transactions can retain locks and row versions, increasing contention. Not all storage engines are transactional, and some DDL statements implicitly commit, so know what operations are actually covered. For a transfer, for example, debit and credit belong in one transaction, and the application must verify that the debit matched one eligible account before proceeding.

A retry can run into duplicate inserts or repeat side effects unless the operation is designed for it. Unique keys, idempotency identifiers, and carefully classified transient errors help make retries safer. Keep external operations such as sending email or charging a remote payment service outside a database transaction unless a coordinated design exists; a SQL rollback cannot undo a request already accepted by another service.

Bind values instead of building SQL strings

Applications must not concatenate untrusted input into SQL text. Use prepared statements or the driver’s parameterized execution API so values are transmitted as values. This protects query structure and handles quoting correctly. Placeholders usually bind values, not table names or sort keywords; when dynamic identifiers are needed, choose them from a fixed allowlist. See SQL injection prevention and MySQL with Node.js.

Write-operation checklist

Before running a write, confirm the target schema and account, list explicit insert columns, and ensure the WHERE condition expresses the intended scope. Preview consequential changes, check constraints and foreign-key effects, use transactions when multiple statements must succeed together, and verify affected-row counts. For bulk updates, test against a copy, schedule carefully, monitor execution time, and be prepared to restore data if the result is wrong.

Safe DML is a combination of precise predicates, integrity rules, transaction boundaries, and application parameter binding. Continue with MySQL transactions, keys and constraints, and database security practices.