Inserting and Updating Data

In this lesson, you'll learn how to modify data in your database. We'll cover inserting new records, updating existing ones, and deleting data you no longer need.

The INSERT Statement

INSERT adds new rows to a table. There are several ways to insert data.

Insert Single Row

-- Create a users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    age INTEGER
);

-- Insert a single user
INSERT INTO users (id, username, email, age)
VALUES (1, 'johndoe', 'john@example.com', 28);

-- Verify the insert
SELECT * FROM users;

Insert Multiple Rows

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    category TEXT
);

-- Insert multiple products at once
INSERT INTO products (id, name, price, category)
VALUES
    (1, 'Laptop', 999.99, 'Electronics'),
    (2, 'Mouse', 24.99, 'Electronics'),
    (3, 'Desk', 299.99, 'Furniture'),
    (4, 'Chair', 199.99, 'Furniture');

-- View all products
SELECT * FROM products;

Partial Inserts

You don't need to provide values for every column:

-- Create a blog posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    published_date TEXT,
    author TEXT
);

-- Insert with only required fields
INSERT INTO posts (id, title, author)
VALUES (1, 'My First Post', 'Alice');

-- Insert with all fields
INSERT INTO posts (id, title, content, published_date, author)
VALUES (2, 'SQL Tutorial', 'Learn SQL basics...', '2025-01-15', 'Bob');

-- Check the results
SELECT * FROM posts;

The UPDATE Statement

UPDATE modifies existing rows in a table. Always use a WHERE clause to specify which rows to update!

Update Single Column

-- Create an inventory table
CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    quantity INTEGER,
    price DECIMAL(10, 2)
);

-- Insert initial data
INSERT INTO inventory (id, product, quantity, price)
VALUES
    (1, 'Laptop', 10, 999.99),
    (2, 'Mouse', 50, 24.99),
    (3, 'Keyboard', 30, 79.99);

-- Update the quantity of laptops
UPDATE inventory
SET quantity = 15
WHERE product = 'Laptop';

-- View the updated inventory
SELECT * FROM inventory;

Update Multiple Columns

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT,
    salary INTEGER,
    department TEXT
);

-- Insert employees
INSERT INTO employees (id, name, position, salary, department)
VALUES
    (1, 'John Doe', 'Developer', 60000, 'Engineering'),
    (2, 'Jane Smith', 'Developer', 65000, 'Engineering'),
    (3, 'Mike Johnson', 'Manager', 75000, 'Sales');

-- Promote John with a new position and salary
UPDATE employees
SET position = 'Senior Developer',
    salary = 75000
WHERE name = 'John Doe';

-- View the results
SELECT * FROM employees;

Update with Calculations

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    discount_percent INTEGER
);

-- Insert products
INSERT INTO products (id, name, price, discount_percent)
VALUES
    (1, 'Laptop', 1000.00, 0),
    (2, 'Mouse', 50.00, 0),
    (3, 'Keyboard', 100.00, 0);

-- Apply a 10% discount to all products
UPDATE products
SET discount_percent = 10,
    price = price * 0.9;

-- View discounted prices
SELECT * FROM products;

Conditional Updates

-- Create a students table
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    grade TEXT,
    score INTEGER
);

-- Insert students
INSERT INTO students (id, name, grade, score)
VALUES
    (1, 'Alice', NULL, 95),
    (2, 'Bob', NULL, 82),
    (3, 'Charlie', NULL, 78),
    (4, 'Diana', NULL, 88);

-- Assign grades based on scores
UPDATE students SET grade = 'A' WHERE score >= 90;
UPDATE students SET grade = 'B' WHERE score >= 80 AND score < 90;
UPDATE students SET grade = 'C' WHERE score < 80;

-- View the results
SELECT * FROM students ORDER BY score DESC;

The DELETE Statement

DELETE removes rows from a table. Like UPDATE, always use WHERE to specify which rows to delete!

Delete Specific Rows

-- Create an orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product TEXT,
    status TEXT,
    amount DECIMAL(10, 2)
);

-- Insert orders
INSERT INTO orders (id, customer, product, status, amount)
VALUES
    (1, 'Alice', 'Laptop', 'completed', 999.99),
    (2, 'Bob', 'Mouse', 'pending', 24.99),
    (3, 'Charlie', 'Keyboard', 'cancelled', 79.99),
    (4, 'Diana', 'Monitor', 'completed', 299.99);

-- Delete cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

-- View remaining orders
SELECT * FROM orders;

Delete with Multiple Conditions

-- Create a logs table
CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    level TEXT,
    created_date TEXT
);

-- Insert log entries
INSERT INTO logs (id, message, level, created_date)
VALUES
    (1, 'App started', 'INFO', '2025-01-01'),
    (2, 'Error occurred', 'ERROR', '2025-01-02'),
    (3, 'Warning issued', 'WARN', '2025-01-03'),
    (4, 'Old error', 'ERROR', '2024-12-15'),
    (5, 'Debug message', 'DEBUG', '2025-01-04');

-- Delete old error logs from 2024
DELETE FROM logs
WHERE level = 'ERROR' AND created_date < '2025-01-01';

-- View remaining logs
SELECT * FROM logs;

Important Safety Tips

Warning: Be very careful with UPDATE and DELETE!

Without a WHERE clause, these commands affect ALL rows:

-- DANGEROUS: Updates all rows!
UPDATE products SET price = 0;

-- DANGEROUS: Deletes all rows!
DELETE FROM products;

Pro Tip: Before running UPDATE or DELETE, run a SELECT with the same WHERE clause to see which rows will be affected!

-- First, check which rows will be updated
SELECT * FROM employees WHERE department = 'Sales';

-- Then, if it looks right, do the update
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

Practice Exercises

Try these exercises using the tables above:

  1. Insert a new employee with your name and details
  2. Update all products in the 'Electronics' category to have a 5% discount
  3. Delete all orders with an amount less than $50
  4. Insert three new students with different scores, then assign their grades
  5. Update the price of a specific product, increasing it by 20%

Key Takeaways

  • INSERT adds new rows to a table
  • You can insert one row or multiple rows at once
  • UPDATE modifies existing rows - always use WHERE to target specific rows
  • DELETE removes rows - always use WHERE unless you want to delete everything
  • Test your WHERE clause with SELECT before running UPDATE or DELETE
  • Updates can use calculations and expressions
  • Never run UPDATE or DELETE without WHERE unless you're absolutely sure!

Next Steps

Now that you can create, read, update, and delete data, you're ready to learn about joins and relationships - connecting data across multiple tables!

Inserting and Updating Data | LearningSQL.org