Update From Select - SQL Server

Update From Select - SQL Server

In this post we will try to insert values into a table using Select statement. You will find all these queries here answered by different readers. I have taken two example tables and tried these queries on those tables.

Create a new table Tab_1

CREATE TABLE Tab_1 (ID INT, Column_2 VARCHAR(10), Name VARCHAR(100))
INSERT INTO Tab_1 (ID, Column_2, Name)
SELECT 11, 'A1', 'Abhi'
UNION ALL
SELECT 22, 'B1', 'Bir'
UNION ALL
SELECT 33, 'C1', 'Chetan'
UNION ALL
SELECT 44, 'D1', 'Damodar'
UNION ALL
SELECT 55, 'E1', 'Eklavya'

Create a new table Tab_2

CREATE TABLE Tab_2 (ID INT, Column_2 VARCHAR(10), Name VARCHAR(100))
INSERT INTO Tab_2 (ID, Column_2, Name)

SELECT 11, 'F1', 'Fardeen'
UNION ALL
SELECT 22, 'G1', 'Gautam'
UNION ALL
SELECT 33, 'H1', 'Harish'
UNION ALL
SELECT 44, 'I1', 'Fardeen'

Select * from tab_1

ID	Column_2	Name
11	A1	Abhi
22	B1	Bir
33	C1	Chetan
44	D1	Damodar
55	E1	Eklavya

Select * from tab_2

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen

Now lets see the Update queries on these above tables:

Example 1 of UPDATE from SELECT using SQL Server:

UPDATE
    tab_1
SET
    tab_1.Column_2 = tab_2.Column_2,
    tab_1.Name =   tab_2.Name
FROM
    tab_1
INNER JOIN
    tab_2
ON
    tab_1.id = tab_2.id

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 2 of UPDATE from SELECT using SQL Server:

MERGE INTO tab_1 T
   USING tab_2 S
      ON T.id = S.id
WHEN MATCHED THEN
   UPDATE
      SET column_2 = S.column_2,
          Name = S.Name;

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 3 of UPDATE from SELECT using SQL Server:

UPDATE Tab_1 SET Column_2=i.Column_2 ,
Name=i.Name FROM (SELECT ID,Column_2,Name FROM Tab_2)i
where i.ID=Tab_1.ID

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 4 of UPDATE from SELECT using SQL Server:

UPDATE tab_1 SET  tab_1.column_2 = o.column_2,
tab_1.Name = o.Name
FROM tab_2 o
join tab_1 on tab_1.id = o.id

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 5 of UPDATE from SELECT using SQL Server:

UPDATE t
   SET t.column_2 = o.column_2,
   t.name = o.name
  FROM tab_1 AS t
         INNER JOIN
       tab_2 AS o
         ON t.id = o.id

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 6 of UPDATE from SELECT using SQL Server:

update
    Tab_1
set
    Tab_1.column_2 = a.column_2,
    Tab_1.name = a.name
from
    Tab_2 a
where
    Tab_1.id = a.ID
 

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya

Example 7 of UPDATE from SELECT using SQL Server:

UPDATE tab_1
SET Tab_1.column_2 = (SELECT tab_2.column_2
FROM tab_2
WHERE tab_2.id = tab_1.id),
Tab_1.Name =
(SELECT tab_2.Name
FROM tab_2
WHERE tab_2.id = tab_1.id)
WHERE EXISTS (SELECT tab_2.column_2
FROM tab_2
WHERE tab_2.id = tab_1.id);

Result:
tab_1

ID	Column_2	Name
11	F1	Fardeen
22	G1	Gautam
33	H1	Harish
44	I1	Fardeen
55	E1	Eklavya