SQL Data Modification | Learn INSERT, UPDATE, DELETE

Managing Database Content with SQL

While Data Definition Language (DDL) builds the house (database structure), Data Manipulation Language (DML) is about furnishing it and managing the occupants (the data itself). This section focuses on the core DML commands — INSERT, UPDATE, and DELETE — which allow you to add new data, modify existing data, and remove unwanted data from your tables.

These three commands are the workhorses of any database-driven application. Think about TokTuk: users sign up (INSERT into Users), video view counts increase (UPDATE Videos), and users delete comments (DELETE from Comments). Mastering DML is essential for building dynamic applications and maintaining accurate data.

Why Master Data Modification (DML)?

✓Dynamic Applications: Enable applications to store, update, and remove user-generated content and operational data.
✓Data Maintenance: Keep information accurate, relevant, and up-to-date over time.
✓Essential CRUD Operations: Forms the core of Create, Read (SELECT), Update, Delete operations fundamental to most software.
✓Technical Interviews: DML commands are frequently tested in SQL assessments for developers, analysts, and DBAs.
✓Backend Development: Directly translates user actions into persistent database changes.

Let's dive into the specifics of how to use INSERT, UPDATE, and DELETE effectively and safely. Remember, modifying data requires care, especially with the crucial WHERE clause!

Essential Data Modification Commands

These three commands form the foundation of data manipulation in SQL:

INSERT INTO

The INSERT INTO statement adds one or more new rows to a table. You specify the target table and the values for the columns in the new row(s).

-- Add a single new user to TokTuk
INSERT INTO Users (username, email, join_date, country)
VALUES ('data_wiz', 'wiz@datamail.com', '2023-10-26', 'CA');

-- Add a new video (assuming user_id 1 exists)
-- Note: We don't specify video_id if it's auto-incrementing
INSERT INTO Videos (user_id, title, description, upload_date)
VALUES (1, 'SQL Joins Explained', 'A deep dive into INNER and OUTER joins.', CURRENT_TIMESTAMP);

You must provide values for columns that do not have a default value and are marked as NOT NULL. The order of values must match the order of columns specified (or the table's default column order if columns aren't listed).

Learn more about INSERT

UPDATE

The UPDATE statement modifies existing data in one or more rows within a table. You specify the table, the columns to change (using the SET clause), and critically, which rows to affect (using the WHERE clause).

-- Correct a user's country
UPDATE Users
SET country = 'US'
WHERE username = 'data_wiz';

-- Increment the view count for a specific video
UPDATE Videos
SET views = views + 1
WHERE video_id = 101;

-- Update multiple columns for users who haven't logged in recently
UPDATE Users
SET account_status = 'inactive', last_login_prompt = CURRENT_DATE
WHERE last_login < date('now', '-90 days'); -- Example syntax for 90 days ago

Crucial: The WHERE clause determines which rows are updated. Omitting it will update all rows in the table, which is rarely intended. Always test UPDATE statements carefully.

Learn more about UPDATE

DELETE FROM

The DELETE FROM statement removes one or more rows from a table. Similar to UPDATE, the WHERE clause is essential for specifying which rows should be deleted.

-- Remove a specific comment by its ID
DELETE FROM Comments
WHERE comment_id = 5432;

-- Delete all interactions associated with a specific video
DELETE FROM Interactions
WHERE video_id = 205;

-- Remove inactive user accounts based on last login
DELETE FROM Users
WHERE account_status = 'inactive'
  AND last_login < date('now', '-1 year'); -- Example: remove if inactive for > 1 year

Extreme Caution: Omitting the WHERE clause in a DELETE statement will remove all rows from the table. This action is usually permanent and devastating in production. Always verify your WHERE clause! Consider using transactions for safety.

Learn more about DELETE

Advanced Data Modification Techniques

SQL offers more ways to manipulate data efficiently and conditionally:

Multi-Row INSERT

Insert multiple rows using a single INSERT statement, which is generally more performant than multiple individual inserts.

-- Add multiple new tags in one go
INSERT INTO Tags (tag_name) VALUES
  ('sql'),
  ('database'),
  ('tutorial');

Details

INSERT with SELECT

Populate a table with data retrieved from another query. Useful for archiving, summarizing, or copying data.

-- Archive old, inactive user data
INSERT INTO ArchivedUsers (user_id, username, email, archived_date)
SELECT user_id, username, email, CURRENT_DATE
FROM Users
WHERE account_status = 'inactive'
  AND last_login < date('now', '-2 years');

Details

UPDATE using Subqueries or Joins

Modify rows in one table based on values or conditions met in related tables. The exact syntax can vary between SQL dialects (e.g., using FROM clause in UPDATE or subqueries in WHERE/SET).

-- Example using Subquery: Reset view count for videos by banned users
UPDATE Videos
SET views = 0, status = 'hidden'
WHERE user_id IN (SELECT user_id FROM Users WHERE account_status = 'banned');

-- Example using JOIN (PostgreSQL/SQL Server syntax might differ)
-- Give bonus points to users who liked popular videos
-- (This syntax is illustrative and varies)
-- UPDATE UserPoints up
-- SET points = points + 10
-- FROM Users u
-- JOIN Interactions i ON u.user_id = i.user_id
-- JOIN Videos v ON i.video_id = v.video_id
-- WHERE up.user_id = u.user_id
--   AND i.interaction_type = 'like'
--   AND v.views > 100000;

Details

DELETE using Subqueries or Joins

Remove rows from a table based on conditions involving other tables.

-- Example using Subquery: Delete interactions for videos by banned users
DELETE FROM Interactions
WHERE user_id IN (SELECT user_id FROM Users WHERE account_status = 'banned');

-- Example using JOIN (Syntax varies): Delete comments on videos older than 1 year
-- DELETE c
-- FROM Comments c
-- JOIN Videos v ON c.video_id = v.video_id
-- WHERE v.upload_date < date('now', '-1 year');

Details

Next Steps & Best Practices

Mastering data modification is key to managing dynamic data. Keep these points in mind:

Always use WHERE: Double-check your WHERE clauses in UPDATE and DELETE statements to avoid unintended widespread changes.
Use Transactions: Wrap related DML operations within a transaction (BEGIN, COMMIT, ROLLBACK) to ensure atomicity and allow for recovery from errors. See the Transactions section.
Understand Constraints: Be aware of PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints. They can cause DML statements to fail if the rules are violated.
Permissions: In real-world scenarios, users need appropriate database privileges (INSERT, UPDATE, DELETE) on tables to execute these commands.
Performance: Modifying large amounts of data can be resource-intensive. Consider indexing, batching operations, and performing bulk modifications during off-peak hours.
Practice: Experiment with these commands on a test database. Try inserting data that violates constraints, updating rows based on complex conditions, and deleting data using subqueries.

Explore the detailed pages for each command linked above and try the practice exercises to solidify your understanding.

SQL Learning

SQL Data Modification (DML)

SQL Learning

SQL Data Modification (DML)

Managing Database Content with SQL

Why Master Data Modification (DML)?

Essential Data Modification Commands

INSERT INTO

UPDATE

DELETE FROM

Advanced Data Modification Techniques

Multi-Row INSERT

INSERT with SELECT

UPDATE using Subqueries or Joins

DELETE using Subqueries or Joins

Next Steps & Best Practices

Loading SQL editor...

Related Topics

SQL Basics (SELECT)

Data Definition (DDL)

Transactions (TCL)