“Update From” Statement in SQL Server

Author posted by Jitendra on Posted on under category Categories SQL, SQL Server and tagged as Tags with 2 Comments on “Update From” Statement in SQL Server

Example to demonstrate the “Update From” Statement in SQL Server. This example can be used in scenarios where developer wants to update the Table with the help of Inner Join.

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

Related posts

  • nikhil rotke

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

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