UPDATE and DELETE Commands

UPDATE Changes Rows; DELETE Removes Rows

UPDATE changes values in existing rows. DELETE removes existing rows. Both are powerful because a single statement can affect one row, thousands of rows or the whole table.

The Five-Step Safe Workflow

1. Protect recovery: use a current backup, tested restore plan or disposable copy. Confirm the table uses a transactional engine when you expect rollback.
2. Start a transaction: keep autocommit from making the change permanent immediately.
3. Preview the exact target: run SELECT with the same WHERE and inspect IDs plus current values.
4. Change and verify: execute UPDATE or DELETE, check affected rows and run a second SELECT.
5. Choose explicitly: COMMIT only when correct; otherwise ROLLBACK.

START TRANSACTION;

SELECT student_id, full_name, marks, status
FROM students
WHERE student_id = 3
FOR UPDATE;

-- Run the intended UPDATE or DELETE here.
-- Verify the result, then choose exactly one:
COMMIT;
-- ROLLBACK;

FOR UPDATE locks selected InnoDB rows until transaction end, helping prevent another transaction from changing them between preview and write. Keep transactions short and understand application concurrency before using locks broadly.

Safe UPDATE Examples

Update one row by primary key

START TRANSACTION;

SELECT student_id, full_name, marks
FROM students
WHERE student_id = 3
FOR UPDATE;

UPDATE students
SET marks = 78.00,
    status = 'Active'
WHERE student_id = 3;

SELECT student_id, full_name, marks, status
FROM students
WHERE student_id = 3;

COMMIT;

Update several rows intentionally

START TRANSACTION;

SELECT student_id, full_name, marks
FROM students
WHERE class_name = 'X-B' AND status = 'Active'
ORDER BY student_id
FOR UPDATE;

UPDATE students
SET marks = LEAST(marks + 1.00, 100.00)
WHERE class_name = 'X-B'
  AND status = 'Active';

SELECT student_id, full_name, marks
FROM students
WHERE class_name = 'X-B' AND status = 'Active'
ORDER BY student_id;

ROLLBACK;

The lab deliberately rolls back. LEAST prevents the calculated mark from exceeding 100, while the table CHECK constraint supplies a second line of protection.

Safe DELETE Examples

First delete only a known practice row, not an arbitrary production student:

START TRANSACTION;

INSERT INTO students (full_name, class_name, marks, status)
VALUES ('Temporary Record', 'TEST', NULL, 'Inactive');

SET @test_student_id = LAST_INSERT_ID();

SELECT student_id, full_name, class_name
FROM students
WHERE student_id = @test_student_id
FOR UPDATE;

DELETE FROM students
WHERE student_id = @test_student_id;

SELECT ROW_COUNT() AS rows_deleted;

SELECT student_id, full_name
FROM students
WHERE student_id = @test_student_id;

COMMIT;

Using the generated primary key makes the target precise. If the preview returns more or fewer rows than expected, stop and rollback before deleting.

Transactions, Autocommit and Rollback

MySQL sessions commonly start with autocommit enabled, so a standalone UPDATE or DELETE can become permanent immediately. START TRANSACTION begins an explicit transaction. COMMIT makes its changes durable; ROLLBACK cancels uncommitted changes.

• Rollback protection requires a transactional storage engine such as InnoDB.
• Some DDL statements cause implicit commits; do not mix schema changes casually into a DML recovery plan.
• A disconnected client may cause an open transaction to roll back, but never use disconnection as the plan.
• After COMMIT, transaction rollback cannot undo the change. Recovery then depends on backups and logs.
• Keep the transaction short to reduce locks and blocking.

SELECT @@autocommit AS autocommit_setting;
SELECT ROW_COUNT() AS affected_by_previous_statement;

Client messages for UPDATE can distinguish rows matched from rows actually changed depending on client flags. Verify target IDs and final values, not only one number.

DELETE vs TRUNCATE vs DROP

TRUNCATE and DROP are not faster spellings for a filtered DELETE. They differ in privileges, locking, logging, foreign-key restrictions, auto-increment effects and transaction behavior. Use them only when the exact administrative intention is clear and recovery is prepared.

Common Mistakes and Protection Layers

• Running write SQL before preview: copy the same WHERE into a SELECT and inspect primary keys.
• Broad text condition: names are not guaranteed unique; prefer a primary key or verified key set.
• Clicking commit automatically: pause after verification and make COMMIT a conscious decision.
• Assuming SQL_SAFE_UPDATES is enough: it is only a session guardrail and can reject safe work or allow unintended key-based work.
• Ignoring NULL logic: WHERE marks = NULL matches nothing; use IS NULL.
• Testing on live data: create a copy or transaction lab and deliberately ROLLBACK.
• No backup restore test: a backup that has never been restored is an unverified recovery plan.
• Concatenated user input: prepared statements are mandatory for application-supplied values.

For additional operational safeguards, continue to Safe UPDATE and DELETE Practices and Transactions.

Safe Practice Lab

1. Create students_lab with CREATE TABLE students_lab LIKE students, then copy the rows using INSERT SELECT.
2. Start a transaction and preview one row by primary key with FOR UPDATE.
3. Update that row, verify it and ROLLBACK. Confirm the old value returned.
4. Insert a temporary row, delete only its generated ID and verify the result.
5. Repeat a multi-row update with a class condition; predict and verify the affected key list.
6. Explain why SQL_SAFE_UPDATES cannot replace a backup and transaction.

Quick Summary

• UPDATE changes values; DELETE removes rows.
• Use a precise WHERE and preview the same condition with SELECT.
• For important work: backup, transaction, lock/preview, change, verify, then commit or rollback.
• DELETE, TRUNCATE and DROP have different scope and recovery behavior.
• Guardrails help, but disciplined review and tested recovery are essential.

Official References

• UPDATE Statement — MySQL 8.4
• DELETE Statement — MySQL 8.4
• COMMIT and ROLLBACK Statements
• InnoDB Locking Reads
• sql_safe_updates System Variable
• TRUNCATE TABLE Statement

References reviewed on 14 August 2026. Practise destructive statements only on disposable data with recovery available.