As a developer working with SQLite databases, there will be many situations where you need to update records in one table using data from another related table. This is a common requirement when you have data spread across multiple tables that needs to be synchronized.
Fortunately, SQLite provides a straightforward way to perform updates across tables using correlated subqueries. In this guide, I’ll walk you through exactly how to do this, with clear examples using real-world data. By the end, you’ll be confidently updating your SQLite tables using data from other tables like a pro! Let’s get started.
The key to updating records in an SQLite table using data from another table is to use a correlated subquery in the SET clause of the UPDATE statement. This allows you to set the value of a column to the result returned by a SELECT query on the related table, matching rows using a condition in the subquery’s WHERE clause.
Setting Up Sample Tables
Before we dive into the UPDATE examples, let’s first set up a couple of simple tables with some sample data that we can work with. Imagine we are working with data from a company’s sales database.
We’ll create two tables – orders and customers:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name TEXT,
email TEXT,
city TEXT
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date TEXT,
total REAL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
The customers table stores customer information, while the orders table stores order details and has a foreign key referencing the customer_id.
Let’s insert some sample data:
INSERT INTO customers (name, email, city)
VALUES
('ABC Corp', '[email protected]', 'New York'),
('XYZ Inc', '[email protected]', 'San Francisco'),
('Acme Co', '[email protected]', 'Chicago');
INSERT INTO orders (customer_id, order_date, total)
VALUES
(1, '2023-01-10', 1250.00),
(2, '2023-02-15', 800.00),
(1, '2023-03-05', 2500.00),
(3, '2023-04-22', 1800.00);
Updating from Another Table
Now let’s look at some examples of how we can update the orders table using data from the customers table.
Example 1: Updating a single column
Suppose we want to add a shipping_city column to the orders table and populate it with the customer’s city for each order.
First, add the new column:
ALTER TABLE orders ADD COLUMN shipping_city TEXT;
Then we can update it using a correlated subquery:
UPDATE orders
SET shipping_city = (
SELECT city
FROM customers
WHERE customers.customer_id = orders.customer_id
);
The subquery selects the city from the customers table where the customer_id matches the customer_id of each order. The result is used to set the value of the shipping_city column.
If we query the orders table now, we’ll see the shipping_city populated:
order_id customer_id order_date total shipping_city
-------- ----------- ---------- -------- -------------
1 1 2023-01-10 1250.0 New York
2 2 2023-02-15 800.0 San Francisco
3 1 2023-03-05 2500.0 New York
4 3 2023-04-22 1800.0 Chicago
Example 2: Updating multiple columns
We can also update multiple columns at once. Let’s add customer name and email columns to orders:
ALTER TABLE orders
ADD COLUMN customer_name TEXT;
ALTER TABLE orders
ADD COLUMN customer_email TEXT;
Now let’s update them using the data from customers:
UPDATE orders
SET
customer_name = (
SELECT name
FROM customers
WHERE customers.customer_id = orders.customer_id
),
customer_email = (
SELECT email
FROM customers
WHERE customers.customer_id = orders.customer_id
);
We use two subqueries in the SET clause, one for each column we want to update. The subqueries select the corresponding name and email columns from the customers table.
The orders table will now contain the customer details:
order_id customer_id order_date total shipping_city customer_name customer_email
-------- ----------- ---------- ------- ------------- ------------- -------------------
1 1 2023-01-10 1250.0 New York ABC Corp [email protected]
2 2 2023-02-15 800.0 San Francisco XYZ Inc [email protected]
3 1 2023-03-05 2500.0 New York ABC Corp [email protected]
4 3 2023-04-22 1800.0 Chicago Acme Co [email protected]
Additional WHERE Clause
We can also add a WHERE clause to the main UPDATE statement to conditionally update only certain rows.
For example, to only update orders from ‘New York’:
UPDATE orders
SET shipping_city = (
SELECT city
FROM customers
WHERE customers.customer_id = orders.customer_id
)
WHERE shipping_city = 'New York';
The subquery is evaluated for each row, but the SET only happens for rows matching the main WHERE condition.
Using ORDER BY and LIMIT
If you’ve built SQLite with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option, you can also use ORDER BY and LIMIT clauses to control which and how many rows get updated.
For instance, to update only the most recent order:
UPDATE orders
SET total = total * 0.9
WHERE customer_id = 1
ORDER BY order_date DESC
LIMIT 1;
This applies a 10% discount to the most recent order for customer 1.
Conclusion
As you can see, SQLite makes it easy to perform updates on one table using correlated subqueries to pull in data from other related tables. This is an enormously useful technique for keeping data synchronized across multiple tables in your database.
The key points to remember:
- Use a correlated subquery in the SET clause to select values from another table
- Add a WHERE clause to the subquery to match rows between tables
- You can update multiple columns at once by using separate subqueries
- Add a WHERE clause to the main UPDATE to conditionally update rows
- Use ORDER BY and LIMIT to control which rows are updated (if enabled)
I hope this guide has given you a thorough understanding of how to update SQLite tables using data from other tables. With these techniques in your toolkit, you’ll be able to keep your data clean, consistent and up-to-date across even the most complex database schemas. Happy SQLite coding!