Mastering INSERT, UPDATE, and DELETE: The Complete Guide to SQL Data Manipulation

Most SQL tutorials focus heavily on SELECT statements—how to read and analyze data. But in the real world, data doesn't just appear out of thin air. You need to create it, update it, and sometimes delete it.

These operations are known as DML (Data Manipulation Language).

In this guide, we'll master the three pillars of DML: INSERT, UPDATE, and DELETE.

1. INSERT: Adding New Data

The INSERT statement is used to add new rows to a table.

Basic Syntax

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

You can also insert multiple rows at once (Bulk Insert):

INSERT INTO table_name (column1, column2)
VALUES 
  (value1, value2),
  (value3, value4);

Interactive Example: Onboarding Users

Let's add some new users to our application.

INSERT ... SELECT: Copying Data

You can also insert data by selecting from another table. This is incredibly useful for data migrations or creating backups.

INSERT INTO target_table (col1, col2)
SELECT col1, col2
FROM source_table
WHERE condition;

2. UPDATE: Modifying Existing Data

The UPDATE statement modifies existing records.

CRITICAL WARNING: Always use a WHERE clause unless you intend to update every single row in the table!

Syntax

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

Interactive Example: Salary Adjustments

Let's give a raise to everyone in the Engineering department.

3. DELETE: Removing Data

The DELETE statement removes rows from a table.

Like UPDATE, if you omit the WHERE clause, you will delete all rows!

Syntax

DELETE FROM table_name
WHERE condition;

Note: DELETE removes rows but keeps the table structure. To remove the table entirely, use DROP TABLE.

Interactive Example: Cleaning Logs

Let's remove logs that are older than 7 days.

Interactive Challenge: The Full CRUD Cycle

CRUD stands for Create, Read, Update, Delete.

Challenge:

1. Insert a new product 'Tablet' with price 300.
2. Update the price of 'Laptop' to 950.
3. Delete the 'Mouse' product.

You have access to: products_dml_challenge

• id (INTEGER)
• name (TEXT)
• price (INTEGER)

-- 1. Insert Tablet
INSERT INTO products_dml_challenge (id, name, price)
VALUES (3, 'Tablet', 300);

-- 2. Update Laptop price
UPDATE products_dml_challenge
SET price = 950
WHERE name = 'Laptop';

-- 3. Delete Mouse
DELETE FROM products_dml_challenge
WHERE name = 'Mouse';

-- Verify
SELECT * FROM products_dml_challenge;

Best Practices

Here are some critical tips to avoid disasters:

1. Always Use WHERE with UPDATE and DELETE

-- ❌ DANGER: Updates ALL rows
UPDATE employees SET salary = 50000;

-- ✅ SAFE: Updates specific rows
UPDATE employees SET salary = 50000 WHERE id = 5;

2. Test with SELECT First

Before running an UPDATE or DELETE, run a SELECT with the same WHERE clause to verify which rows will be affected.

-- Step 1: Check what will be deleted
SELECT * FROM users WHERE last_login < '2024-01-01';

-- Step 2: If it looks good, delete
DELETE FROM users WHERE last_login < '2024-01-01';

3. Use Transactions for Safety

Wrap your DML in a transaction so you can rollback if something goes wrong:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If everything looks good:
COMMIT;

-- If something went wrong:
-- ROLLBACK;

4. Be Careful with NULL

-- This WON'T work as expected
DELETE FROM users WHERE email = NULL;

-- Use IS NULL instead
DELETE FROM users WHERE email IS NULL;

Conclusion

You've now mastered the core DML commands!

• INSERT: Adds data.
• UPDATE: Changes data (Don't forget WHERE!).
• DELETE: Removes data (Don't forget WHERE!).

These three commands, combined with SELECT, form the foundation of almost every database interaction you'll ever write.