How to Update a Table Data From Another Table in SQLite

SQLite is an embedded database that doesn’t use a database like Oracle in the background to operate. The SQLite offers some features which are that it is a serverless architecture, quick, self-contained, reliable, full-featured SQL database engine. SQLite does not require any server to perform queries and operations on the database.

In this article, we will see the stepwise implementation of how we can update a table with the data from another table in SQLite.

Introduction to UPDATE Statement in SQLite

UPDATE command is used to alter to change the content of the table. Updating a table using the Data from another table becomes important when there is a mistake present in the previous table. The mistake can be minor or it can be major, due to this a temporary and separate table can be made and all the data from Table One can be moved to Table Two.

Stepwise Implementation

Before understanding the implementation process, it is recommended to create and populate a table in SQLite. For this tutorial, we will be using a table called students and populate it with values. Write and execute the following.

Query:

CREATE TABLE Students 
(
Student_ID INTEGER,
FirstName TEXT,
LastName TEXT,
Class INTEGER,
Section TEXT
);

Explanation: After creation of the table. Now we will insert some records into the Students table.

Query:

INSERT INTO Students VALUES(10,'Vivek','Singh',7,'B');
INSERT INTO Students VALUES(12,'Manish','Roy',8,'A');
INSERT INTO Students VALUES(15,'Dilip','Mukherjee',10,'A');
INSERT INTO Students VALUES(16,'Souvik','Sen',10,'B');
INSERT INTO Students VALUES(18,'Rohit','Das',10,'A');
INSERT INTO Students VALUES(21,'Mohit','Shetty',9,'A');
INSERT INTO Students VALUES(22,'Raj','Banerjee',9,'B');
INSERT INTO Students VALUES(24,'Biswajit','Das',7,'B');
INSERT INTO Students VALUES(25,'Srijit','Roy',8,'A');
INSERT INTO Students VALUES(27,'Rakesh','Chatterjee',8,'C');

Now we will see the stepwise implementation of How we can Update another Table Using the Values of this Table.

Step 1: Create Another Table With Different Name

Firstly, we will create another table with a name which is different than our already existing table. User can choose names which might resemble to the table or completely different. Write and execute the following command to create the second table.

Query:

CREATE TABLE Temp_Students 
(
Student_ID INTEGER,
FirstName TEXT,
Class INTEGER
);

Explanation: Here, the table Temp_Student is being created which has some common columns with the main Students table. Now we will update this table with the help of data from the Students table.

Step 2: Fetch Data Exist in Both Table

Now, we will fetch the data from the columns of Students table which is common with the second table. We will use the INSERT INTO command and pass the Table name and the columns of the second table. Then using the SELECT statement we will fetch the values of those respective common columns from the Students table.

Query:

INSERT INTO Temp_Students 
(
Student_ID, FirstName, Class
) 
SELECT Student_ID, FirstName, Class FROM Students;

Step 3: Show All Content of Second Table

Now we will use the SELECT statement to print the entire content of the Temp_Students table and check if the values are being fetched properly or not.

Query:

SELECT * FROM temp_students;

Output:

Output

Explanation: As we can see, all the respective data from the Students table has been added into the Temp_Students table.

Conclusion

As we saw in this article, how we can update a table with the data from another table in SQLite. Updating a table using the data of another table becomes necessary when the developer wants to keep some part of the old table into the new table. This approach will save a lot more time because without writing multiple INSERT INTO statements, only one INSERT INTO statement is enough to fetch all the data from the previous table.