Section 10: Data Manipulation Language

Modifying Database Contents

This section focuses on Data Manipulation Language (DML) statements, which are used to modify the data stored within your database tables. You will learn how to add new records using INSERT, change existing records with UPDATE, and remove records using DELETE. These commands are the core tools for updating a database.

§1 The INSERT Statement #

The INSERT statement is used to add one or more new rows of data (records) into a table.

§1.1 Adding New Rows to a Table (Specific Columns) #

You can specify which columns you want to insert data into. This is useful when you don’t have values for all columns, or when some columns have default values.

Syntax:

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

NOTE: The Browser SQLite instance for these lectures is ephemeral and auto-updating, every change you make will re-run the SQL commands, meaning and INSERT statements will run again. Refreshing the page will reset all changes.

INSERT Example (Specific Columns):
Add a new customer, providing only their name. The id column (key) is auto-incrementing, so it can be omitted. All other columns will have null or default values. Only the name has a NOT NULL schema constraint in this table, so it must always be set.

§1.2 Inserting All Columns #

If you are providing values for all columns in the table in the exact order they are defined in the table schema, you can omit the column list. However, it’s generally safer and more readable to explicitly list the columns, even when inserting a full row.

Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);

INSERT Example (All Columns):
Add a new product, providing values for all columns.

Note how the id (key) is not an increment of the other id values, the only schema constraint for id is the key is NOT NULL and UNIQUE, so an arbitrary value is accepted. If id is omitted (using explicit column syntax), the database will autoincrement to the next valid ID.

§1.3 Inserting Multiple Rows #

Many SQL databases allow you to insert multiple rows in a single INSERT statement, which can be more efficient than executing several individual INSERT statements.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1a, value2a, ...),  -- row1
    (value1b, value2b, ...),  -- row2
    ...;                      -- etc...

INSERT Example (Multiple Rows):
Add two new categories in one statement.

§1.4 Inserting Data from Another SELECT Statement #

You can populate a table with data retrieved from another table or query using an INSERT ... SELECT statement. This is powerful for data migration or creating summary tables.

Syntax:

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

INSERT Example (from SELECT):
Assume you have a new_products table with potential new products to sell. You can insert products from new_products into the products table that meet certain criteria.
In this example, we will only insert items into the products table if they are worth more than $50.

§2 UPDATE Statement #

The UPDATE statement is used to modify existing data in a table.

Syntax:

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

UPDATE Example (with WHERE):
Change the unit_price of a specific product.

§2.1 Importance WHERE Clause With UPDATE #

The WHERE clause is critical in an UPDATE statement. If you omit the WHERE clause, all rows in the table will be updated with the specified new values. This can lead to irreversible data loss. Always test UPDATE statements with a SELECT query using the same WHERE clause first, to ensure you are targeting the correct rows.

UPDATE Example (without WHERE - DANGER!):
This will update the unit_price for every single product in the products table because the WHERE clause was omitted.

Every single row in the table had its unit_price updated because the WHERE statement was omitted.

§2.2 Updating Multiple Columns #

You can update multiple columns in a single UPDATE statement by separating the column = new_value pairs with commas.

UPDATE Example (Multiple Columns):
Update both the unit_price and category_id for a product.

§3 DELETE Statement #

The DELETE statement is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

§3.1 Importance of WHERE Clause #

Similar to UPDATE, the WHERE clause is essential for DELETE statements. If you omit the WHERE clause, all rows in the table will be deleted. This action is often irreversible without a backup. Always verify your DELETE ... WHERE clause with a SELECT statement first.

DELETE Example (with WHERE):
Remove a specific customer from the customers table.

DELETE Example (without WHERE - DANGER!):
This would delete every single customer from the customers table.

DELETE FROM customers;  --dont do this.
-- Must *always* add a WHERE clause with DELETE

§4 Safely Performing UPDATE and DELETE Operations #

Before executing an UPDATE or DELETE statement, it is strongly recommended to always use a SELECT statement to verify the exact rows that will be affected. Here is an example workflow for performing updates/deletes safely.

For this example: we will delete all inactive users who have not logged in in a long time.

First, start with a SELECT statement paired with a WHERE condition to identify rows you want to target:

SELECT user_id, username, last_login_date
FROM Users
WHERE last_login_date < '2023-01-01' AND is_active = FALSE;

Review the results of this SELECT statement carefully, after you have confirmed that ONLY expected rows are being returned, you can change the SELECT statement to a DELETE.

DELETE FROM Users
WHERE last_login_date < '2023-01-01' AND is_active = FALSE;

Always follow this simple procedure before running any UPDATE or DELETE operations, this will protect you from making unintended modifications/deletions.

§5 More Resources #

INSERT

UPDATE

DELETE

§6 Questions #

Please number each response to directly correspond with the question. Provide clear, concise answers that fully address each question without unnecessary elaboration or essay-style writing.

  1. What is the purpose of the INSERT statement?
  1. When inserting a new row, why is it generally safer to explicitly list the columns even if you are providing values for all of them?
  1. Can a single INSERT statement add more than one row?
  1. What is the primary purpose of the UPDATE statement?
  1. Why is the WHERE clause important when using UPDATE and DELETE statements?
  1. If you execute UPDATE products SET unit_price = 0; (without a WHERE clause), what exactly will happen?
  1. Do you use the DELETE statement to remove a column?
  1. Why should you use a SELECT statement before running any UPDATE or DELETE statements?
  1. Write an INSERT statement that adds a new supplier named “Acme Widgets” with phone “555-1234” into the suppliers table, which has columns id, name, and phone.
  1. You need to copy every customer from the old_customers table into the customers table, but only if their status is ‘active’. Provide the single SQL statement that accomplishes this. The customers table has the columns: name, phone, email, status
  1. Write an UPDATE statement that raises the unit_price of every product in category 3 by 10%. The products table has the columns: id, name, unit_price, category_id, supplier_id, sku
  1. What happens if you run DELETE FROM products WHERE category_id = 3; and no products have category_id = 3?
  1. Provide the SQL to insert two new categories, ‘Electronics’ and ‘Books’, in a single statement. The categories table has 1 column: name