“Update From” Statement in SQL Server

In This article, i am going to demonstrate that how to use the “Update From” statement in SQl Server. Inner Join or Joins can be easily used in Insert Statement, but some times it is needed in Update Statements Also.

Lets have the below example:

In Table 1, i have the List of Projects and in Table2, i have the Ids of the Table and New Project Names.

Table 1 have following records:

SQL Server Table 1
Table 1

I Want to update all those Project Names which are present in Table 2.

So, below is the complete script used to achieve this problems solution in SQL Server.

DECLARE @Project TABLE (Id INT,PName VARCHAR(30))

INSERT INTO @Project VALUES (1,'AA')
INSERT INTO @Project VALUES (2,'BB')
INSERT INTO @Project VALUES (3,'CC')
INSERT INTO @Project VALUES (4,'DD')

SELECT * FROM @Project

DECLARE @NewNames TABLE (Id INT,NName VARCHAr(30))
INSERT INTO @NewNames VALUES (3,'New CC')
INSERT INTO @NewNames VALUES (4,'New DD')

UPDATE
	@Project
SET
	PName = O.NName
FROM
	@Project P
	INNER JOIN @NewNames O ON O.Id = P.Id

SELECT * FROM @Project

Final Output:

SQl Server Answer
Answer

2 comments

  1. SET
    PName = O.NName
    FROM
    @Project P
    INNER JOIN @NewNames O ON O.Id = P.Id
    ————————————————————
    sir i m not getting how this portion actually get execute ..

    1. HI Nikhil,
      @Project is the DataTable.

      SET
      PName = O.NName
      FROM
      @Project P
      INNER JOIN @NewNames O ON O.Id = P.Id

      Normally we right “update table set column=value”
      But in above example i am using the “SET” keyword of SQL server in another way, which works as update statement when we join the table with another temp table.
      I have used the datatable instead of temp table because it is light weight.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.