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:
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:
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:
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:
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.
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.