UPDATE: Modifying Existing Data

CRUD is an acronym representing the four fundamental database operations: Create, Read, Update, and Delete.

Programming

One Record, Four Possible Operations

A database application manages data repeatedly throughout the data's existence. A record may be added, retrieved, changed, and eventually removed. These four fundamental operations are known together as CRUD: Create, Read, Update, and Delete. The focus of this article is UPDATE, the operation used when an existing record is modified.

The Complete Data Lifecycle

CRUD provides a complete framework for thinking about data management. Create adds a new record, Read retrieves data, Update modifies an existing record, and Delete removes a record. Together, these operations describe how data moves through its lifecycle in database applications.

How do Create, Read, Update, and Delete connect to form the complete lifecycle of data in a database?

In the real world

Consider a customer record in an e-commerce database. The record can first be created, later read by the application, updated when existing customer information changes, and eventually deleted. This example shows why CRUD is described as a lifecycle rather than as a single database action.

CRUD and SQL Commands

Each CRUD operation maps directly to a SQL command. This mapping connects the general idea of managing data with the concrete commands that databases understand and execute.

CRUD operation	Purpose	SQL command
Create	Add a new record	INSERT
Read	Retrieve data	SELECT
Update	Modify an existing record	UPDATE
Delete	Remove a record	DELETE

The four CRUD operations and their corresponding SQL commands

Which SQL command corresponds to each CRUD operation?

What Changes During UPDATE

An UPDATE operation acts on data that already exists. The record remains part of the database, but one or more of its existing values are modified. The important distinction is that UPDATE changes an existing record; it does not represent the creation of a new record.

What changes in a database record when an UPDATE operation modifies existing field values?

Worked example

Classifying a changed customer record

An existing customer record in an e-commerce database is modified.

1
Identify the record's status
The scenario says that the customer record already exists, so this is not a Create operation.
2
Identify the action
The record is being modified rather than merely retrieved or removed.
3
Map the operation to SQL
Modifying an existing record is Update, and Update maps to the SQL command UPDATE.

Result

CRUD operation: Update. SQL command: UPDATE.

Choosing the Operation

To classify a data-management scenario, focus on the action being performed. Ask whether the application is adding a new record, retrieving data, modifying an existing record, or removing a record. After identifying the action, use the CRUD-to-SQL mapping to select the corresponding command.

Scenario	CRUD operation	SQL command
A user retrieves their balance	Read	SELECT
A new product record is added	Create	INSERT
An existing customer record is modified	Update	UPDATE
An email record is removed	Delete	DELETE
Temperature data is retrieved	Read	SELECT
A new patient record is added	Create	INSERT

Classifying common data-management scenarios

EASY

Classify each scenario as Create, Read, Update, or Delete, then name the matching SQL command: an existing customer record is changed.

Hints

Look for whether the record already exists.
The key action is changed or modified.
After identifying the CRUD operation, use the CRUD-to-SQL mapping.

Your attempt (not saved)

Output

Answer: Update, which maps to UPDATE.

Mistakes in CRUD Classification

Common mistake
Treating every database action as an UPDATE
What it looks like
A user retrieves their balance.
Why it fails
Retrieving data is Read, not Update.
Do this instead
Map Read to SELECT.
Common mistake
Confusing a new record with a modified record
What it looks like
A new product record is added.
Why it fails
The scenario describes Create because the record is new.
Do this instead
Map Create to INSERT.
Common mistake
Confusing removal with modification
What it looks like
An email record is removed.
Why it fails
Removing data is Delete, not Update.
Do this instead
Map Delete to DELETE.
Common mistake
Remembering the CRUD word but not the SQL mapping
What it looks like
Calling the operation Update but selecting INSERT as its SQL command.
Why it fails
Each CRUD operation has a specific corresponding SQL command.
Do this instead
Memorize the complete mapping: Create–INSERT, Read–SELECT, Update–UPDATE, Delete–DELETE.

Best practice

Separate two questions when reading a scenario: What happened to the data, and which SQL command represents that action? This prevents the operation's meaning from being confused with its command name.

Key Takeaways

1
CRUD stands for Create, Read, Update, and Delete.
2
UPDATE is the CRUD operation for modifying an existing record.
3
The SQL commands map directly as follows: Create to INSERT, Read to SELECT, Update to UPDATE, and Delete to DELETE.
4
CRUD forms a complete framework for understanding how data is managed throughout its lifecycle.
5
To classify a scenario, identify whether data is being added, retrieved, modified, or removed.

At a glance

CRUD describes the four fundamental database operations: Create, Read, Update, and Delete.
UPDATE modifies an existing record rather than adding, retrieving, or removing one.
The SQL command for Update is UPDATE.
The complete CRUD mapping is Create–INSERT, Read–SELECT, Update–UPDATE, and Delete–DELETE.
Identifying the action in a scenario is the first step toward choosing the correct CRUD operation.

More concepts starting with U