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