How to INSERT, UPDATE, and DELETE Data in SQL

Once you understand how to query data with SELECT, the next step is learning how to modify data in your database. That means inserting new records, updating existing ones, and deleting data when it’s no longer needed.

In this beginner-friendly guide, we’ll walk through how to use the three most essential SQL data manipulation statements: INSERT, UPDATE, and DELETE. You’ll learn how to use them safely, with real-world examples and best practices.

Want to try these examples in your browser? Fire up the Galaxy SQL Editor to test queries with no setup.

INSERT: Add New Data

The INSERT statement adds new rows to a table.

Basic Syntax:

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


Example:

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');


This creates a new user with the name Alice and her email address.

You can insert multiple rows at once:

INSERT INTO users (name, email)
VALUES
 ('Bob', 'bob@example.com'),
 ('Charlie', 'charlie@example.com');


Best Practices:

• Always specify column names.
• Validate data types before inserting.
• Use RETURNING * (PostgreSQL) to confirm what was added.

UPDATE: Modify Existing Data

The UPDATE statement changes values in one or more rows.

Basic Syntax:

UPDATE table_name
SET column1 = value1
WHERE condition;


Example:

UPDATE users
SET email = 'alice.new@example.com'
WHERE name = 'Alice';


This updates Alice’s email address.

Update Multiple Fields:

UPDATE users
SET name = 'Alicia', updated_at = NOW()
WHERE id = 5;


Important:

Always use a WHERE clause to avoid updating every row!

-- Bad: Updates every user
UPDATE users SET is_active = false;


Best Practices:

• Use WHERE to scope your updates.
• Use transactions (BEGIN, COMMIT) when changing multiple rows.
• Back up critical data before large updates.

DELETE: Remove Data

The DELETE statement removes rows from a table.

Basic Syntax:

DELETE FROM table_name
WHERE condition;


Example:

DELETE FROM users WHERE id = 42;


This deletes the user with ID 42.

Like UPDATE, DELETE requires careful use of the WHERE clause.

-- Warning: Deletes every row in the table
DELETE FROM users;


Best Practices:

• Always double-check your WHERE clause.
• Use LIMIT in some databases (e.g., MySQL) to delete in batches.
• Consider archiving instead of deleting when needed.

Real-World Use Cases

1. User Signups (INSERT)

When someone fills out a signup form, use INSERT to create a new user record.

2. Profile Updates (UPDATE)

When a user edits their profile, use UPDATE to save their changes.

3. Account Deletion (DELETE)

When a user requests to delete their account, use DELETE (or a soft delete via UPDATE).

Common Pitfalls

1. Missing WHERE in UPDATE or DELETE

This is one of the most dangerous mistakes and can affect all rows in a table.

2. Inserting Mismatched Columns and Values

Ensure the number of columns matches the number of values:

-- Error: 2 columns but only 1 value
INSERT INTO users (name, email) VALUES ('Alice');


3. Data Type Confusion

Check that you're inserting the correct data types (INTEGER, TEXT, BOOLEAN, etc). Refer to our SQL Data Types guide for help.

4. Deleting the Wrong Records

Review conditions carefully before executing destructive queries.

Safety Tips

• Always test UPDATE or DELETE queries with a SELECT first:

SELECT * FROM users WHERE id = 10;


• Use BEGIN TRANSACTION and ROLLBACK to safely test changes.
• Avoid modifying production data without a backup or rollback plan.

Final Thoughts

Being able to INSERT, UPDATE, and DELETE data is foundational to managing databases. These statements give you full control over your records—but with great power comes great responsibility.

By following best practices and using safeguards, you’ll avoid data loss and make confident, precise changes.

Want to practice with real queries in a safe sandbox? Launch the Galaxy SQL Editor to try it yourself.

Continue learning: