Updating and Deleting Data

Introduction

UPDATE changes existing rows; DELETE removes them. Forgetting WHERE can touch every row in a table—one of the most expensive mistakes in production. This chapter writes safe updates and deletes, compares DELETE vs TRUNCATE, and enables sql_safe_updates for protection.

Prerequisites

SELECT Queries Basics
Sample data in blog_dev.users and blog_dev.posts

UPDATE Syntax

Change one user display name:

sql

UPDATE users
SET display_name = 'Ada Lovelace'
WHERE id = 1;

Code explanation:

SET lists columns and new values
WHERE restricts rows—always verify with SELECT first

Preview before update:

sql

SELECT id, display_name FROM users WHERE id = 1;
 
UPDATE users
SET display_name = 'Ada Lovelace'
WHERE id = 1;
 
SELECT id, display_name FROM users WHERE id = 1;

Update multiple columns:

sql

UPDATE posts
SET
  title = 'Hello MySQL (revised)',
  published_at = NOW()
WHERE id = 1;

UPDATE With Expression

Increment-style (numeric column example):

sql

UPDATE users
SET is_active = IF(is_active = 1, 1, 1)
WHERE id = 2;

Real pattern with counter column:

sql

-- If view_count column existed:
-- UPDATE posts SET view_count = view_count + 1 WHERE id = 1;

UPDATE With JOIN (MySQL)

Update posts titles for one author:

sql

UPDATE posts p
INNER JOIN users u ON u.id = p.user_id
SET p.title = CONCAT('[Archived] ', p.title)
WHERE u.email = 'bob@example.com'
  AND p.published_at IS NULL;

Code explanation:

Join identifies rows to update—still need selective WHERE

DELETE Rows

Remove one post:

sql

DELETE FROM posts WHERE id = 2;

Delete all drafts (still scoped):

sql

DELETE FROM posts WHERE published_at IS NULL;

Verify count:

sql

SELECT COUNT(*) FROM posts;

DELETE vs TRUNCATE

	DELETE	TRUNCATE
WHERE	Yes	No—all rows
Triggers	Fires	Does not fire
Rollback	Row-by-row in transaction	DDL-like reset
Speed	Slower large tables	Fast table empty
FK	Row rules apply	May fail if referenced

Empty table for re-seed:

sql

TRUNCATE TABLE posts;

Recreate sample posts from Inserting Data if needed.

Warning

DELETE Without WHERE Deletes All Rows

sql

DELETE FROM posts;   -- removes every row

Always run SELECT … WHERE with same condition first.

sql_safe_updates Mode

Session guard against keyless updates/deletes:

sql

SET sql_safe_updates = 1;

Now update/delete without WHERE using key or LIMIT fails:

sql

-- ERROR 1175 without WHERE on indexed column or LIMIT
UPDATE users SET is_active = 0;

Safe patterns:

sql

UPDATE users SET is_active = 0 WHERE id = 3;
 
UPDATE users SET is_active = 0 WHERE id IN (1, 2) LIMIT 2;

Check current setting:

sql

SELECT @@sql_safe_updates;

Disable for current session only if you know what you are doing:

sql

SET sql_safe_updates = 0;

Soft Delete Pattern (App Design)

Instead of physical delete, flag row:

sql

ALTER TABLE users ADD COLUMN deleted_at DATETIME NULL;
 
UPDATE users SET deleted_at = NOW() WHERE id = 4;
 
SELECT * FROM users WHERE deleted_at IS NULL;

Common in web apps—keeps history and simplifies undo.

Practice Checklist

Update one row by primary key
Delete rows with WHERE
Try sql_safe_updates and confirm unsafe statement blocked
Never run unqualified DELETE on production

FAQ

Rows matched vs changed?

UPDATE with same value: matched but changed count 0—MySQL reports both.

Delete parent with child posts?

Without ON DELETE CASCADE FK, delete fails or orphans—constraints chapter.

Undo DELETE?

Only via transaction ROLLBACK if not committed—or restore backup.

TRUNCATE reset AUTO_INCREMENT?

Usually resets counter to 1—verify if you rely on ids.

UPDATE LIMIT?

MySQL allows UPDATE … LIMIT n—useful with ordering for batch jobs.

ORM delete?

Hibernate/JPA generates DELETE—still verify WHERE in logs during dev.