SQL DML Commands: SELECT, INSERT, UPDATE & DELETE

What is DML? DML stands for Data Manipulation Language - the SQL commands that work with the data inside your tables. In the previous chapter you built the tables (DDL); now you'll learn to fill them, read from them, change them, and clean them up. These four commands - SELECT, INSERT, UPDATE, DELETE - are the ones you'll use in 90% of your daily SQL work.

We'll use this products table throughout all four topics:

Setup

CREATE TABLE products (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(100) NOT NULL,
    category  VARCHAR(50),
    price     DECIMAL(8,2) NOT NULL,
    stock     INT DEFAULT 0,
    created   DATE
);

1 SELECT

Top

The SELECT statement retrieves data from a table. It's the most-used SQL command by far - you'll write it hundreds of times a day as a developer or analyst. (We cover it in-depth with WHERE, ORDER BY, and GROUP BY in the next chapter; here we focus on the fundamentals.)

Select All Columns

SQLSELECT * FROM products;

Result

id | name            | category    | price  | stock | created
---|-----------------|-------------|--------|-------|----------
1  | Wireless Mouse  | Electronics | 29.99  | 150   | 2025-01-10
2  | SQL Textbook    | Books       | 45.00  | 80    | 2025-02-15
3  | USB-C Hub       | Electronics | 39.99  | 200   | 2025-03-01
4  | Notebook Pack   | Stationery  | 12.50  | 500   | 2025-03-20
5  | Mechanical KB   | Electronics | 89.99  | 60    | 2025-04-01

Select Specific Columns

Don't use * in production code - always list the columns you need:

SQL

SELECT name, price, stock
FROM products;

Result

name            | price  | stock
----------------|--------|------
Wireless Mouse  | 29.99  | 150
SQL Textbook    | 45.00  | 80
USB-C Hub       | 39.99  | 200
Notebook Pack   | 12.50  | 500
Mechanical KB   | 89.99  | 60

Aliases: Rename Columns in Output

SQL

SELECT
    name            AS product_name,
    price           AS unit_price,
    price * stock   AS inventory_value
FROM products;

Result

product_name    | unit_price | inventory_value
----------------|------------|----------------
Wireless Mouse  | 29.99      | 4498.50
SQL Textbook    | 45.00      | 3600.00
USB-C Hub       | 39.99      | 7998.00
Notebook Pack   | 12.50      | 6250.00
Mechanical KB   | 89.99      | 5399.40

DISTINCT: Remove Duplicates

SQL

-- How many unique categories do we have?
SELECT DISTINCT category FROM products;

Result

category
-----------
Electronics
Books
Stationery

LIMIT: Restrict the Number of Rows

SQL

-- Show only the first 3 products
SELECT name, price FROM products
LIMIT 3;

Avoid SELECT * in real applications. It fetches every column, wastes bandwidth, and breaks your code if someone adds a column later. Always list the specific columns you need. SELECT * is fine for quick exploration and debugging only.

Key Takeaways

SELECT column1, column2 FROM table retrieves specific data
Use AS to rename columns and create calculated columns in the output
DISTINCT removes duplicate values from the results
LIMIT n restricts how many rows are returned
Avoid SELECT * in production - name your columns explicitly

2 INSERT

Top

The INSERT statement adds new rows to a table. Every piece of data in your database got there through an INSERT (or a bulk import, which is just many INSERTs under the hood).

Insert a Single Row

SQL

INSERT INTO products (name, category, price, stock, created)
VALUES ('Webcam HD', 'Electronics', 59.99, 75, '2025-04-10');

Result

Query OK, 1 row affected.   (id = 6 auto-generated)

Insert Multiple Rows at Once

Much faster than running individual INSERTs:

SQL

INSERT INTO products (name, category, price, stock, created) VALUES
    ('Desk Lamp',     'Office',      34.99,  120, '2025-04-12'),
    ('Sticky Notes',  'Stationery',  5.99,   800, '2025-04-12'),
    ('Monitor Stand', 'Office',      49.99,  45,  '2025-04-15');

Result

Query OK, 3 rows affected.

Insert with DEFAULT and NULL Values

SQL

-- Let stock use its DEFAULT (0) and leave created as NULL
INSERT INTO products (name, category, price)
VALUES ('Mystery Box', 'Misc', 19.99);

Insert from a SELECT (Copy Data)

Copy data from one table into another - useful for backups or data migration:

SQL

-- Copy all electronics into an archive table
INSERT INTO products_archive (name, category, price)
SELECT name, category, price
FROM products
WHERE category = 'Electronics';

Pro tip: When inserting multiple rows, a single INSERT INTO ... VALUES (...), (...), (...) is much faster than running three separate INSERT statements. Some databases can insert thousands of rows in a single statement.

Key Takeaways

INSERT INTO table (columns) VALUES (values) adds a new row
Insert multiple rows in one statement by comma-separating the value sets
Omit columns to use their DEFAULT values (or NULL if allowed)
INSERT INTO ... SELECT copies data between tables

3 UPDATE

Top

The UPDATE statement modifies existing data in a table. It's how you correct mistakes, change statuses, adjust prices, and keep your data current.

Update a Single Row

SQL

-- Raise the price of the SQL Textbook
UPDATE products
SET price = 49.99
WHERE id = 2;

Result

Query OK, 1 row affected.
Rows matched: 1  Changed: 1

Let's verify:

SQLSELECT name, price FROM products WHERE id = 2;

Result

name          | price
--------------|------
SQL Textbook  | 49.99   <-- was 45.00

Update Multiple Columns at Once

SQL

-- Change the name and restock the Wireless Mouse
UPDATE products
SET name = 'Wireless Mouse Pro',
    stock = 300
WHERE id = 1;

Update Multiple Rows

SQL

-- 10% price increase for all Electronics
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

Result

Query OK, 3 rows affected.   (all Electronics products updated)

Update Using Calculations

SQL

-- Decrease stock by 10 for products that just shipped
UPDATE products
SET stock = stock - 10
WHERE id IN (1, 3, 5);

Always use WHERE with UPDATE. Running UPDATE products SET price = 0 without a WHERE clause will set the price to zero for every row in the table. This is one of the most common and devastating SQL mistakes. Always double-check your WHERE clause before executing.

Safety tip: Before running an UPDATE, run the same query as a SELECT first to see which rows will be affected: SELECT * FROM products WHERE category = 'Electronics'. If the results look right, swap SELECT for UPDATE.

Key Takeaways

UPDATE table SET column = value WHERE condition changes existing data
You can update multiple columns in one statement: SET col1 = val1, col2 = val2
Use calculations in SET: SET price = price * 1.10
Never run UPDATE without WHERE unless you intentionally want to change every row
Test with SELECT first to preview which rows will be affected

4 DELETE

Top

The DELETE statement removes rows from a table. Unlike DROP (which destroys the table itself) or TRUNCATE (which empties the entire table), DELETE lets you target specific rows.

Delete Specific Rows

SQL

-- Remove a single product by ID
DELETE FROM products
WHERE id = 9;

Result

Query OK, 1 row affected.

Delete Multiple Rows with a Condition

SQL

-- Remove all products with zero stock
DELETE FROM products
WHERE stock = 0;

-- Remove all Stationery products
DELETE FROM products
WHERE category = 'Stationery';

Delete with IN and Subqueries

SQL

-- Delete products that have never been ordered
DELETE FROM products
WHERE id NOT IN (
    SELECT DISTINCT product_id FROM orders
);

DELETE vs TRUNCATE vs DROP

Command	What It Removes	WHERE Clause	Rollback	Speed
`DELETE`	Specific rows (or all)	Supported	Can rollback in a transaction	Slower (logs each row)
`TRUNCATE`	All rows (keeps table)	Not supported	Cannot rollback	Very fast
`DROP`	Table + all data	N/A	Cannot rollback	Instant

Same rule as UPDATE: always use WHERE. Running DELETE FROM products without a WHERE clause deletes every row in the table. Use SELECT first to verify which rows will be deleted, then replace SELECT * with DELETE.

Soft delete vs hard delete: Many production applications don't actually DELETE rows. Instead, they add an is_deleted column and UPDATE it to TRUE. This lets you "undo" deletions and keep audit trails. You'll see this pattern often in real-world databases.

Key Takeaways

DELETE FROM table WHERE condition removes specific rows
DELETE logs each row and can be rolled back inside a transaction
Never run DELETE without WHERE unless you mean to empty the table
Use TRUNCATE (not DELETE) when you want to empty an entire table fast
Consider soft deletes (is_deleted = TRUE) in production applications

What's next? Up next is DQL Commands. Keep going to build on what you just learned in DML Commands.

All Chapters in SQL Beginner Course

Introduction to SQL (3 topics) Database Concepts (3 topics) SQL Fundamentals (3 topics) DDL Commands (5 topics) DML Commands (4 topics) You're here DQL Commands (4 topics) SQL Joins (5 topics) SQL Functions (2 topics)

Continue Learning

Go beyond this course

→ Views, Stored Procedures & Triggers (Intermediate) → Database Normalization (Intermediate) → SQL Intermediate Course (Full Course)