UPDATE and DELETE Rows in MySQL

Once data is in your tables, you will inevitably need to change or remove it. The UPDATE statement modifies existing rows, and DELETE removes them. Both commands are powerful and permanent, so understanding how to use them safely is critical.

Basic UPDATE Syntax

UPDATE requires the target table, a SET clause with column-value pairs, and a WHERE clause to identify which rows to change. Without WHERE, every row in the table will be modified.

UPDATE employees
SET salary = 80000
WHERE employee_id = 15;

Updating Multiple Columns

Separate column assignments with commas. You can also reference the current value of a column in the expression.

UPDATE employees
SET salary = salary * 1.10,
    department = 'Senior Engineering',
    updated_at = NOW()
WHERE department = 'Engineering'
  AND hire_date < '2022-01-01';

UPDATE with JOIN

MySQL allows you to join tables inside an UPDATE statement. This lets you modify rows based on related data without a subquery.

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.15
WHERE c.membership = 'gold';

Basic DELETE Syntax

DELETE removes rows that match the WHERE condition. Like UPDATE, omitting the WHERE clause deletes all rows from the table -- a mistake you definitely want to avoid.

DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2023-01-01';

DELETE with LIMIT

You can limit the number of rows deleted in a single statement. This is useful for batch deletions on large tables to avoid locking the table for too long.

DELETE FROM log_entries
WHERE created_at < '2023-06-01'
ORDER BY created_at ASC
LIMIT 10000;

Safe Practices

Enable MySQL's safe update mode (SET sql_safe_updates = 1;) during development. It prevents UPDATE and DELETE without a WHERE clause that uses a key column. Before running a destructive statement, run a SELECT with the same WHERE clause first to verify which rows will be affected. Always back up critical data and use transactions when making bulk changes so you can roll back if something goes wrong.

-- Preview before deleting
SELECT COUNT(*) FROM orders
WHERE status = 'cancelled'
  AND order_date < '2023-01-01';

-- Wrap in a transaction for safety
START TRANSACTION;
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2023-01-01';
-- ROLLBACK; or COMMIT;