Sometimes we need to update the data of one table with the data from another table in SQL. It can be done in different ways. We can use stored procedure, single SQL update statement or else. This article explains how to update one table by another table in SQL query. Summary of the article:
- Table Creation
- Data Insertion
- SQL Query to Update One Table with Another Table
- Output
Suppose we have two tables (Student1 and Student2). We want to update the data of Student1 table with Student2 table.
Table Creation
At first we need to create Student1 & Student2 tables.It can be done in different ways. We can create the table in database directly or can use temporary table. For simplicity here we will use table variable. Following SQL scripts can be used to declare table variable.
DECLARE @Students1 TABLE ( StudentID int, StudentName nvarchar(100), Addresss nvarchar(150), Phone nvarchar(50) ) DECLARE @Students2 TABLE ( StudentID int, StudentName nvarchar(100), Addresss nvarchar(150), Phone nvarchar(50) )
The above table has only four columns (StudentID, StudentName, Addresss, Phone). You can use less or more columns.
Data Insertion
After table creation we need some sample data. So, insert some demo data into the above two tables. You can use the following SQL scripts:
INSERT INTO @Students1 VALUES(1,'Name1-1','Address1-1','Phone1-1') INSERT INTO @Students1 VALUES(2,'Name2-1','Address2-1','Phone2-1') INSERT INTO @Students1 VALUES(3,'Name3-1','Address3-1','Phone3-1') INSERT INTO @Students2 VALUES(1,'Name1-2','Address1-2','Phone1-2') INSERT INTO @Students2 VALUES(2,'Name2-2','Address2-2','Phone2-2') INSERT INTO @Students2 VALUES(3,'Name3-2','Address3-2','Phone3-2')
If we run a normal a normal SQL query to select all the data from the two tables we will get the following result. This is for observing the current status of the tables. For that sample SQL code is given bellow:
SELECT * FROM @Students1 SELECT * FROM @Students2
SQL Query to Update One Table with Another Table
In order to update the data of Student1 table with the data of Student2 table writes the following SQL quary and execute it:
UPDATE S1 SET S1.StudentName = S2.StudentName, S1.Addresss = S2.Addresss, S1.Phone = S2.Phone FROM @Students2 AS S2 INNER JOIN @Students1 AS S1 ON S2.StudentID = S1.StudentID
Here only simple SQL joins and SQL update commands are used.
Output
If we check the current status of the table, we will get the updated data. Simple SQL select query is given bellow:
SELECT * FROM @Students1 SELECT * FROM @Students2
In this way we can update one table by another table using SQL query.
Fine for update one table by another table. This is a good example.
its good.thanks..