UPDATE…FROM usage in TSQL

Sometimes we have to use the column value which we will be using in the where clause of the same update statement. At that time FROM clause statement would be very helpful. FROM clause is used to specify a table, view, or derived table source to provide the criteria for the update operation.

In the below code sample, we use Employee Roles table to update Employee Table but only for the employees who are developers. We use a FROM clause in the update query in which we get the Employee roles and then in the WHERE clause we get check for a developer role and update the Employees table correspondingly.

Code is self explanatory and comments are provided in necessary areas for more easiness.

/************************** Create TestDB Database ****************************/ 

IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO
 
USE [TestDB]
GO
 

/************************** Create Employee Table ****************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Employees')
BEGIN
    DROP TABLE [dbo].[Employees];
END
GO
 
CREATE TABLE [dbo].[Employees](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL,
    [EmpRole] [nvarchar](32) NULL,
    [EmpRoleDate] [datetime] NULL
)
GO


/************************** Create Employee Table ****************************/ 

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'EmployeeRoles')
BEGIN
    DROP TABLE [dbo].[EmployeeRoles];
END
GO

CREATE TABLE [dbo].[EmployeeRoles](
    [EmpRoleId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [EmpRole] [nvarchar](32) NOT NULL,
    [AchievedDate] [datetime] NOT NULL
)
GO


/********************** Enter Employee Roles Details ***********************/

INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('R1');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('R2');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('R3');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('R4');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('Y1');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('Y2');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('Y3');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('Y4');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('T1');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('T2');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('T3');
INSERT INTO [dbo].[Employees] ([EmpName]) VALUES ('T4');
GO
 

/********************** Enter Employee Roles Details ***********************/ 

INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (1,'Dev','2012-12-17');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (2,'Dev','2012-12-16');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (3,'Sdet','2012-12-15');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (4,'Dev','2012-12-14');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (5,'Test','2012-12-13');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (6,'Sdet','2012-12-12');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (7,'Test','2012-12-11');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (8,'Test','2012-12-10');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (9,'Dev','2012-12-09');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (10,'Sdet','2012-12-08');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (11,'Dev','2012-12-07');
INSERT INTO [dbo].[EmployeeRoles] ([EmpId],[EmpRole],[AchievedDate]) VALUES (12,'Test','2012-12-06');
GO
 

/******************* Update Employees who are only Devs *******************
Here we want to update employees (who are devs) EmpRoleDate and EmpRolw with 
AchievedDate and EmpRole from EmployeeRoles table based on EmpId.
**************************************************************************/

UPDATE [dbo].[Employees]
              SET [EmpRoleDate] = EmpRoleData.[AchievedDate],
                  [EmpRole] = EmpRoleData.[EmpRole]
FROM (
              SELECT
                   [EmpId],
                   [EmpRole],
                   [AchievedDate]
              FROM
                  [dbo].[EmployeeRoles]
      )  EmpRoleData
WHERE
       [dbo].[Employees].[EmpId] = EmpRoleData.EmpId
       AND
       EmpRoleData.[EmpRole] = 'Dev';
GO


/********************** Select Employee Details and check ***********************/
SELECT 
      [EmpId],[EmpName],[EmpRoleDate],[EmpRole]
FROM
      [dbo].[Employees];
GO
 

/**************************** Drop TestDB Database ******************************/
USE master
GO
 

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

Output –

UpdateFrom

You may also like...

  • P Madhu

    -begin tran
    –update p set p.PECCode=pu.ASD
    — from DBname..table1 p,dbname..table2 pu
    –where p.channel_id=pu.channelid
    –commit

    We can also update like this…