MySQL Tutorial: Updating and Deleting Data with UPDATE and DELETE

![[Mysql 강좌] 8강 UPDATE와 DELETE로 데이터 수정·삭제하기](/uploads/editor/Mysql-%EA%B0%95%EC%A2%8C-8%EA%B0%95-UPDATE%EC%99%80-DELETE%EB%A1%9C-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%88%98%EC%A0%95-%EC%82%AD%EC%A0%9C%ED%95%98%EA%B8%B0-20260706-4eb4c4.webp)
Data Is Constantly Changing
Data stored in a database isn't static—it's frequently modified after initial storage.
For example:
A user might update their email address
A post title could be edited
A comment might be deleted
In short,
Data modification and deletion are common operations alongside storage.
This lesson covers the SQL commands for these actions.
What Is UPDATE?
UPDATE is the command used to modify existing data.
Examples include:
Changing a member's name
Editing a post title
Increasing view counts
Updating product prices
All these operations use UPDATE.
Basic UPDATE Syntax
The most fundamental structure is:
UPDATE table_name
SET column_name = value
WHERE condition;
The most critical part of UPDATE is the WHERE clause.
Omitting WHERE can lead to unintended consequences.
Updating a Member's Name
For instance, let's change the name of member ID 1:
UPDATE member
SET name = 'Kim Min-su'
WHERE id = 1;
This updates only the name for the member with ID 1.
Updating Multiple Columns
You can modify multiple columns in one statement:
UPDATE member
SET
name = 'Hong Gildong',
email = 'hong@test.com'
WHERE id = 1;
This is common in real-world scenarios where multiple fields need updating simultaneously.
Post Editing Example
You can edit both title and content of a post:
UPDATE board
SET
title = 'MySQL Lecture',
content = 'Content has been updated.'
WHERE id = 3;
This is how forum post editing features work internally.
The Critical Importance of WHERE in UPDATE
Consider this SQL:
UPDATE member
SET name = 'Admin';
No WHERE clause is present.
What happens when executed?
All members' names would change to 'Admin.'
This is one of the most common mistakes, even among professionals.
Always verify your WHERE condition before executing UPDATE statements.
What Is DELETE?
DELETE removes data from a database.
Examples include:
User account deactivation
Deleting posts
Removing comments
Deleting products
All these operations use DELETE.
Basic DELETE Syntax
The simplest form is:
DELETE FROM table_name
WHERE condition;
As with UPDATE, the WHERE clause is crucial.
Deleting a Post
To delete post ID 10:
DELETE FROM board
WHERE id = 10;
The deleted data will no longer appear in queries.
Deleting a Member
To remove member ID 3:
DELETE FROM member
WHERE id = 3;
This is how most user deactivation features operate.
The Most Dangerous DELETE Mistake
Consider this SQL:
DELETE FROM member;
No WHERE clause present.
Executing this would:
Delete all records in the member table.
The table structure remains, but all data is removed.
This is a critical error to avoid in professional settings.
Developing Safe Practices for UPDATE and DELETE
In professional environments, it's standard practice to first run SELECT queries before modifying or deleting:
SELECT *
FROM member
WHERE id = 5;
Verify the selected data, then execute:
UPDATE member
SET name = 'Kim Cheolsu'
WHERE id = 5;
Or:
DELETE FROM member
WHERE id = 5;
This simple habit prevents most errors.
Recommended Workflow for UPDATE and DELETE
Professional workflows typically follow this sequence:
Verify data with SELECT
↓
Confirm conditions
↓
Execute UPDATE or DELETE
↓
Re-check results with SELECT
Consistently following this process is essential for safe database management.
How Forum Editing Works
The process of editing a post follows these steps:
User clicks Edit
↓
Enters new title and content
↓
Clicks Save
↓
Executes UPDATE SQL
↓
Edit confirmed
When you click the edit button on a website, an UPDATE command runs internally.
How Post Deletion Works
Deleting a post follows this sequence:
Click Delete button
↓
Confirm deletion
↓
Execute DELETE SQL
↓
Redirect to list view
Because DELETE is irreversible, most systems include an additional confirmation step.
Key Differences Between UPDATE and DELETE
Command | Purpose |
|---|---|
UPDATE | Modifies existing data |
DELETE | Removes data completely |
UPDATE changes content while preserving the record.
DELETE removes records entirely from the database.
Common Mistakes for Beginners
1. Forgetting to Include WHERE Clause
This is the most dangerous error.
Always verify WHERE conditions in both UPDATE and DELETE statements.
2. Entering Incorrect Conditions
For example, entering:
id = 1
Instead of:
id = 11
This could modify unintended records.
3. Skipping the Pre-Check SELECT Statement
Always verify which data will be affected before modifying or deleting.
4. Assuming Deleted Data Can Be Restored
DELETE operations are difficult to recover after execution.
In professional settings, backups and transaction management are used, but beginners should always confirm deletion before executing the command.
Key Takeaways from This Lesson
UPDATE modifies existing data in a database.
DELETE removes data completely from a database.
Both commands require careful use of WHERE clauses.
Always verify affected records with SELECT before modifying or deleting.
Omitting WHERE can modify or delete all records in the table.
These are critical operations requiring professional caution.
In Conclusion
This lesson covered how to use UPDATE and DELETE commands for data modification and removal in MySQL databases.
You've now learned all fundamental database management functions: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE).
Next, we'll explore PRIMARY KEY, AUTO_INCREMENT, and INDEX, essential concepts for database design and performance optimization.
FAQ
Why is the WHERE clause so important in UPDATE and DELETE?
Without a WHERE clause, UPDATE or DELETE operations will affect all records in the table instead of just the intended ones, potentially causing significant data loss.
What's the safest way to modify or delete database records?
Always run a SELECT query first to verify which records will be affected, then proceed with UPDATE or DELETE only after confirming the correct conditions.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [Mysql 강좌] 8강 UPDATE와 DELETE로 데이터 수정·삭제하기