How to Update Table Rows in SQLite Using Subquery

SQLite is a lightweight, serverless RDBMS that is used to store and manipulate the data in the database in relational or tabular form. It can run on various operating systems like Windows, macOS, Linux and mobile operating systems like Android and iOS. SQLite is designed for single-user access. Multiple processes can read from an SQLite database simultaneously but only one process can write at a time. In this article, we are going to see how we can update table rows in SQLite using subquery.

Introduction to Update Statement in SQLite

The UPDATE statement is a SQLite command that is used to update or change the already existing record from the table. Update Statement allows to make changes to one or more columns within a specified table based on specific conditions such as conditions defined in the WHERE clause.

Basic Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Explanation:

table_name: It is a name of the table from which records will updated.
column1 and column2: It represent the columns within the table that will updated.
value1 and value2: They are the new values to be assigned to the respective columns.

Setting Up Environment

Let us start by creating a table and adding some sample data to the table. The following query creates two sample tables and inserts records in them:

Query:

//first table creation
CREATE TABLE test1
(
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO test1 VALUES(1, 'Alex', 20);
INSERT INTO test1 VALUES(4, 'Jane', 34);
INSERT INTO test1 VALUES(9, 'Tyler', 11);

//Second table creation
CREATE TABLE test2
(
  id INT,
  name VARCHAR(20),
  age INT
);

INSERT INTO test2 VALUES(1, 'Austin', 25);
INSERT INTO test2 VALUES(5, 'Jesse', 34);
INSERT INTO test2 VALUES(9, 'Tyler', 23);
INSERT INTO test2 VALUES(3, 'Smith', 11);

After Inserting data the table test1 looks like:

test1 initial data

After Inserting data the table test2 looks like:

test2 initial data

Update Rows Using Subquery

We can make use of UPDATE statement with a subquery to update tables based on some complex logic. The subquery allows us to interact with other tables among other during the update statement.

The following query updates the test1 table records which are present in test2 table with the values of the test2 table:

Query:

UPDATE test1
SET 
  name=t.name,
  age=t.age
FROM (
  SELECT * FROM test2
) t
WHERE test1.id=t.id;

Output:

Updated data in test1

Explanation: As we can see the records with ids 1 and 9 were updated in the table.

Example of How to Update Table Rows in SQLite Using Subquery

Let’s create some tables and insert some data in it. The following query creates a department table and inserts some records in it.

Query:

-- Create departments table
CREATE TABLE departments 
(
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- Insert sample data into departments table
INSERT INTO departments (dept_name) VALUES
    ('Engineering'),
    ('Sales'),
    ('Marketing');

Output:

Department table

Let’s creates a another table called employee and inserts some records in it.

Query:

-- Create employees table
CREATE TABLE employees 
(
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Insert sample data into employees table
INSERT INTO employees (emp_name, dept_id) VALUES
    ('John Doe', 1),  -- Engineering department
    ('Jane Smith', 2), -- Sales department
    ('Michael Johnson', 1),  -- Engineering department
    ('Emily Davis', 3);  -- Marketing department

Output:

employees table

Let’s creates a another table called salaries and inserts some records in it.

Query:

-- Create salaries table
CREATE TABLE salaries (
    salary_id SERIAL PRIMARY KEY,
    emp_id INT NOT NULL,
    salary_amount NUMERIC(10, 2) NOT NULL,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);

-- Insert sample data into salaries table
INSERT INTO salaries (emp_id, salary_amount) VALUES
    (1, 50000.00),  -- John Doe's salary
    (2, 60000.00),  -- Jane Smith's salary
    (3, 55000.00),  -- Michael Johnson's salary
    (4, 52000.00);  -- Emily Davis's salary

Output:

salaries table

We will write query to update the salary of all the employees in the ‘Engineering‘ department by increasing the salary by 10%. We can make use of UPDATE statement with subquery to achieve this. We will first filter the employee id of all the employees in the ‘Engineering’ department using subquery and later use that information in the UPDATE statement to modify the salary. The following query updates the salary of all the employees in ‘Engineering‘ department by 10%:

Query:

UPDATE salaries
SET salary_amount = salary_amount * 1.1
WHERE emp_id IN (
    SELECT emp_id
    FROM employees
    WHERE dept_id = (
        SELECT dept_id
        FROM departments
        WHERE dept_name = 'Engineering'
    )
);

Output:

updated salary data

Explanation: As we can see the salary of employee 1, i.e. John Doe, increased from 50000 to 55000 and that of employee 3, i.e. Michael Johnson, increased from 55000 to 60500.

Conclusion

After reading whole article now we have good understanding of how to update data in SQLite using Subquery. In this article we have seen a example and perform queries to get good understanding. Now you can easily update the data into the table using the subquery.

Article Tags :

Databases

SQLite

SQLite Query