UPDATE
UPDATE modifies matching rows. Use parameter binding from your app, LIMIT for safety on hand-run queries, and JOIN updates to copy data between tables.
Single, JOIN, LIMIT, atomic increment
EXAMPLE
-- Basic
UPDATE users SET name = 'Ada Lovelace' WHERE id = 1;
-- Multiple columns + expressions
UPDATE products
SET price = price * 1.10,
last_priced_at = NOW()
WHERE category = 'gadgets';
-- JOIN update — copy data from another table
UPDATE posts p
JOIN users u ON u.id = p.user_id
SET p.author_email = u.email,
p.author_name = u.name
WHERE p.author_email IS NULL;
-- Safety: LIMIT — caps damage from a hand-run query
UPDATE accounts SET balance = balance - 100 WHERE id = 42 LIMIT 1;
-- Atomic increment / decrement (great for counters)
UPDATE posts SET views = views + 1 WHERE id = 7;
UPDATE counters SET seq = seq + 1 WHERE name = 'orders';
SELECT LAST_INSERT_ID(seq) FROM counters WHERE name = 'orders';
-- Conditional update — set only if currently equal (optimistic locking)
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE id = ? AND version = ?;
-- If rowcount == 0, somebody else won the race.
-- ALWAYS enable strict SQL modes
SET sql_mode = 'STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE';
Why it matters
Optimistic locking via a version column is one of the cheapest concurrency tools you have. No row locks, no deadlocks — just verify with WHERE version = ? and retry on miss.
Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.
Discussion
Loading…