Update Values from an Existing Column in a Table with SQLite3 and Python

Introduction

SQLite is a popular, self-contained, zero-configuration database library written in C. It’s designed to be easy to use and understand, making it a great choice for rapid development and prototyping. In this article, we’ll explore how to update values from an existing column in a table using SQLite3 and Python.

The Problem

Let’s consider the following two tables:

Table 1

Table 2

We want to add a new column item to Table 1 and update the values in this column with data from Table 2. The resulting table should look like this:

Table 1

The Solution

The solution lies in using a correlated subquery. A correlated subquery is a query that references the outer query’s table, unlike a non-correlated subquery which does not reference any outer queries.

In this case, we want to update the item column in Table 1 with data from Table 2. We can do this by using an UPDATE statement with a correlated subquery:

UPDATE table1
    SET item = (SELECT t2.item FROM table2 t2 WHERE t2.id = table1.id);

Let’s break down what’s happening here:

• UPDATE table1: We’re updating the values in Table 1.
• SET item = ...: We’re setting the new value of the item column to be retrieved from a subquery.
• (SELECT t2.item FROM table2 t2 WHERE t2.id = table1.id): This is our correlated subquery. It references the outer query’s Table 1, using the id column as a link between the two tables.

How it Works

The correlated subquery works by iterating over each row in Table 1 and matching it with the corresponding row in Table 2 based on the id column. For each match, the subquery returns the value of the item column from Table 2, which is then used to update the item column in Table 1.

The Code

Here’s an example code snippet that demonstrates how to use a correlated subquery to update values from an existing column in a table:

import sqlite3

# Connect to SQLite database. It will be created automatically if it doesn't exist.
con = sqlite3.connect(":memory:")
cur = con.cursor()

# Create the tables
cur.execute("CREATE TABLE table1 (id INTEGER, item TEXT, price REAL)")
cur.execute("CREATE TABLE table2 (id INTEGER, item TEXT)")

# Insert some data into the tables
cur.execute("INSERT INTO table1 VALUES (1, 'book', 20.0)")
cur.execute("INSERT INTO table1 VALUES (2, 'copy', 30.0)")
cur.execute("INSERT INTO table1 VALUES (3, 'pen', 10.0)")

cur.execute("INSERT INTO table2 VALUES (1, 'book')")
cur.execute("INSERT INTO table2 VALUES (2, 'copy')")
cur.execute("INSERT INTO table2 VALUES (3, 'pen')")

# Update the item column in table1 with data from table2
cur.execute("""
    UPDATE table1
        SET item = (SELECT t2.item FROM table2 t2 WHERE t2.id = table1.id)
""")

# Commit the changes
con.commit()

# Fetch and print the updated values from table1
cur.execute("SELECT * FROM table1")
rows = cur.fetchall()
for row in rows:
    print(row)

# Close the connection to SQLite database
con.close()

Conclusion

In this article, we’ve explored how to update values from an existing column in a table using SQLite3 and Python. We used a correlated subquery to achieve this, which is a powerful technique for solving complex queries that require referencing outer queries’ tables.

We also provided an example code snippet that demonstrates how to use a correlated subquery to update values from an existing column in a table.

Last modified on 2023-12-20