INSERT, UPDATE, DELETE Operations

Difficulty: Basic
Generated on: 2025-07-10 02:22:10
Category: SQL Cheatsheet for Database Development

SQL INSERT, UPDATE, DELETE Cheatsheet (Basic Level)

This cheatsheet provides a practical guide to basic data manipulation operations in SQL: INSERT, UPDATE, and DELETE. It’s designed to be a quick reference for developers working with relational databases.

1. Quick Overview

Operation	Description	When to Use
INSERT	Adds new rows of data into a table.	When you need to add new records to your database, such as new users, products, or orders.
UPDATE	Modifies existing data in a table.	When you need to change the values of existing records, such as updating a user’s email address, changing a product’s price, or marking an order as shipped.
DELETE	Removes rows of data from a table.	When you need to remove records from your database, such as deleting inactive users, removing discontinued products, or cancelling orders.

2. Syntax

INSERT

-- Inserting a single row with specified columns
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

-- Inserting a single row with all columns (order matters!)
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- Inserting multiple rows
INSERT INTO table_name (column1, column2)
VALUES
    (value1_1, value2_1),
    (value1_2, value2_2),
    (value1_3, value2_3);

-- Inserting data from another table
INSERT INTO table_name (column1, column2)
SELECT column_a, column_b
FROM another_table
WHERE condition;

UPDATE

-- Updating rows based on a condition
UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

-- Updating all rows (use with caution!)
UPDATE table_name
SET column1 = value1;

DELETE

-- Deleting rows based on a condition
DELETE FROM table_name
WHERE condition;

-- Deleting all rows (use with extreme caution!)
DELETE FROM table_name;

-- Deleting all rows and resetting identity column (TRUNCATE is faster)
TRUNCATE TABLE table_name;

3. Common Use Cases

Operation	Use Case Example
INSERT	Adding a new customer to a `Customers` table with name, email, and address.
UPDATE	Changing the price of a product in a `Products` table.
DELETE	Removing an inactive user from a `Users` table.
INSERT	Adding an order to an `Orders` table and linking it to the customer and product.
UPDATE	Updating the shipping status of an order in an `Orders` table.
DELETE	Removing a product from the `Products` table when it is discontinued.
INSERT	Adding a new comment to a `Comments` table linked to a specific article.
UPDATE	Editing a comment in a `Comments` table.
DELETE	Removing a comment from a `Comments` table if it violates community guidelines.

4. Best Practices

Use Transactions: Wrap INSERT, UPDATE, and DELETE operations within transactions to ensure atomicity (all or nothing). This is crucial for data integrity.

-- Example Transaction (MySQL)
START TRANSACTION;
INSERT INTO table_name (column1) VALUES (value1);
UPDATE table_name SET column2 = value2 WHERE column1 = value1;
COMMIT;  -- Or ROLLBACK to undo changes

Use Parameterized Queries (Prepared Statements): Prevent SQL injection vulnerabilities by using parameterized queries. This is typically done via your application’s database library, not directly in SQL. Example (in Python with psycopg2 for PostgreSQL):

import psycopg2

conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")
cur = conn.cursor()

# Correct way to insert data using parameterized query
sql = "INSERT INTO users (username, email) VALUES (%s, %s)"
data = ("johndoe", "john.doe@example.com")
cur.execute(sql, data)

conn.commit()
cur.close()
conn.close()

Index Columns Used in WHERE Clauses: Improve performance of UPDATE and DELETE operations by indexing columns used in the WHERE clause.
Limit the Scope of Updates and Deletes: Always use a WHERE clause to target specific rows. Avoid accidentally updating or deleting all rows in a table.
Backup Data Regularly: Have a backup strategy in place to recover from accidental data loss.
Use TRUNCATE TABLE carefully: TRUNCATE TABLE is faster than DELETE FROM table_name because it deallocates the entire data pages. However, it cannot be rolled back and resets identity columns.
Batch Inserts: For large data imports, consider batch inserting rows to reduce overhead.
Consider Partitioning: For very large tables, partitioning can improve the performance of DELETE operations by allowing you to drop entire partitions.

5. Examples

Sample Data:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Price DECIMAL(10, 2),
    StockQuantity INT
);

INSERT INTO Products (ProductID, ProductName, Price, StockQuantity) VALUES
(1, 'Laptop', 1200.00, 10),
(2, 'Mouse', 25.00, 50),
(3, 'Keyboard', 75.00, 30),
(4, 'Monitor', 300.00, 20);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Email VARCHAR(255)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES
(1, 'John', 'Doe', 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'jane.smith@example.com');

Example INSERT:

-- Adding a new product
INSERT INTO Products (ProductID, ProductName, Price, StockQuantity)
VALUES (5, 'Webcam', 50.00, 40);

-- Inserting a new customer
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (3, 'Peter', 'Jones', 'peter.jones@example.com');

Example UPDATE:

-- Increasing the price of the Laptop by 10%
UPDATE Products
SET Price = Price * 1.10
WHERE ProductName = 'Laptop';

-- Updating a customer's email address
UPDATE Customers
SET Email = 'jane.newemail@example.com'
WHERE CustomerID = 2;

Example DELETE:

-- Removing a product with low stock
DELETE FROM Products
WHERE StockQuantity < 5;

-- Removing a customer (use with caution, consider archiving instead)
DELETE FROM Customers
WHERE CustomerID = 3;

Example INSERT from SELECT:

-- Creating a table to store discounted products
CREATE TABLE DiscountedProducts (
    ProductID INT,
    ProductName VARCHAR(255),
    DiscountedPrice DECIMAL(10, 2)
);

-- Inserting products with a price greater than $100 into the discounted products table with a 10% discount
INSERT INTO DiscountedProducts (ProductID, ProductName, DiscountedPrice)
SELECT ProductID, ProductName, Price * 0.9
FROM Products
WHERE Price > 100;

6. Common Pitfalls

Forgetting the WHERE Clause: This is the most common and dangerous mistake. Always double-check your WHERE clause before executing UPDATE or DELETE statements.
SQL Injection: Failing to use parameterized queries can expose your application to SQL injection attacks.
Data Type Mismatches: Ensure that the data types of the values you are inserting or updating match the data types of the corresponding columns.
Constraint Violations: Violating foreign key, unique, or not-null constraints can cause INSERT, UPDATE, or DELETE operations to fail. Check the constraint definitions and data before execution.
Deadlocks: In concurrent environments, UPDATE and DELETE operations can lead to deadlocks. Use transactions and consider the order in which you access tables to minimize the risk of deadlocks.
Incorrect Date/Time Formats: Date and time formats can be database-specific. Use the correct format for your database.
Null Values: Be mindful of handling null values in WHERE clauses. Use IS NULL or IS NOT NULL instead of = or <> to compare with null.

Troubleshooting Tips:

Check Error Messages: Pay close attention to the error messages returned by the database. They often provide clues about the cause of the problem.
Test in a Development Environment: Always test your INSERT, UPDATE, and DELETE statements in a development environment before running them in production.
Use SELECT Statements to Verify: Before running an UPDATE or DELETE statement, use a SELECT statement with the same WHERE clause to verify that you are targeting the correct rows.

7. Database Variations

While the basic syntax for INSERT, UPDATE, and DELETE is generally consistent across different SQL databases, there are some variations:

MySQL:
- AUTO_INCREMENT for auto-incrementing primary keys.
- ON DUPLICATE KEY UPDATE clause for handling duplicate key violations during INSERT.
- LIMIT clause in DELETE statements to limit the number of rows deleted.
PostgreSQL:
- SERIAL data type for auto-incrementing primary keys (replaced by IDENTITY in more recent versions).
- RETURNING clause to return values from inserted, updated, or deleted rows.
- ON CONFLICT clause for handling constraint violations during INSERT.
SQL Server:
- IDENTITY property for auto-incrementing primary keys.
- OUTPUT clause to return values from inserted, updated, or deleted rows.
- TOP clause in DELETE statements to limit the number of rows deleted.
Oracle:
- Sequences for generating unique values for primary keys.
- RETURNING clause to return values from inserted, updated, or deleted rows.
- FOR UPDATE clause to lock rows for exclusive access during updates.

Example: PostgreSQL RETURNING clause

-- Insert a new product and return the generated ProductID
INSERT INTO Products (ProductName, Price, StockQuantity)
VALUES ('New Product', 100.00, 50)
RETURNING ProductID;

Example: MySQL ON DUPLICATE KEY UPDATE

-- Insert a new product, or update the stock quantity if the ProductID already exists
INSERT INTO Products (ProductID, ProductName, Price, StockQuantity)
VALUES (1, 'Laptop', 1200.00, 15)
ON DUPLICATE KEY UPDATE StockQuantity = VALUES(StockQuantity);

This cheatsheet provides a solid foundation for performing basic data manipulation operations in SQL. Remember to consult the documentation for your specific database for more detailed information and advanced features. Always prioritize data integrity and security when working with databases.