SqlDatabaseBeginner

UPDATE

Modifies existing rows in a table.

Review the syntaxStudy the examplesOpen the coding app
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Modifies existing rows in a table.

The UPDATE statement is a Data Manipulation Language (DML) command used to modify existing records within a relational database table. Internally, most database engines perform an update by locating the specific row, locking it to maintain ACID compliance, and then writing the new data to the data page. In MVCC-based systems like PostgreSQL, an UPDATE actually marks the old row as 'dead' and inserts a new version of the row, which can lead to table bloat if not managed by background maintenance tasks. Performance is heavily dependent on the presence of an index on the columns used in the WHERE clause; without one, the engine must perform a full table scan, significantly increasing execution time and locking duration. For date-related updates, always use ISO 8601 strings (e.g., '2023-10-27') or engine-specific functions to ensure reliable ordering and parsing. Edge cases include updating columns to their current values, which may still fire triggers and generate transaction logs, and handling NULL values, where 'SET column = NULL' is required as comparison operators in the SET clause do not behave like predicates.

Quick reference

Syntax

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Inputs

Parameters

table_nameTarget table to update.
SETColumn assignments to apply.
WHERE condition (optional)Filter for which rows to update.

See it in practice

Examples

1

Updating a Single Column with a Predicate

UPDATE users SET status = 'active' WHERE user_id = 1042;
Output:
UPDATE 1

Updates the 'status' column specifically for the record matching the unique identifier 1042.

2

Performing Arithmetic and Multiple Column Updates

UPDATE products SET price = price * 1.05, last_updated = '2023-11-01' WHERE category = 'electronics';
Output:
UPDATE 12

Applies a 5% price increase to all products in the electronics category and updates a timestamp using an ISO string.

3

Update with a Subquery

UPDATE employees SET department_id = (SELECT id FROM departments WHERE name = 'Engineering') WHERE email = 'staff@example.com';
Output:
UPDATE 1

Dynamically retrieves a foreign key from the 'departments' table to update the employee's record.

Debug faster

Common Errors

1

LogicError

Cause: Omitting the WHERE clause, which causes the database to apply the change to every single row in the table.

Fix: Always include a WHERE clause to target specific records, and consider running a SELECT with the same criteria first to verify the impact.

UPDATE accounts SET balance = 0; -- Resets everyone's balance to zero
2

ConstraintViolation

Cause: Attempting to update a column to a value that violates a UNIQUE, NOT NULL, or FOREIGN KEY constraint.

Fix: Check existing table constraints before updating or use logic to ensure values remain unique and valid.

UPDATE users SET email = 'taken@example.com' WHERE id = 5; -- Fails if email column has a UNIQUE constraint

Runtime support

Compatibility

All SQL databasesSQL-92 (Standard)

RETURNING support varies by DB

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Modifies existing rows in a table.

ame: Target table to update. SET: Column assignments to apply. WHERE condition: Filter for which rows to update.

LogicError: Always include a WHERE clause to target specific records, and consider running a SELECT with the same criteria first to verify the impact. ConstraintViolation: Check existing table constraints before updating or use logic to ensure values remain unique and valid.