Introduction

In this post I will explain how to update bulk columns in SQL server table. My previous post Javascript PAN Number validation in Asp.net Textbox onkeypress event, SQL Query to find Nth Highest or Lowest salary of an employee table.

Explanation

Sometimes we may need to update some columns in a table from another related tables.So here am using joins for update the columns in a table.In stored procedures it will be very useful and this will avoid cursor looping for bulk updates.It will reduce the execution timing.

SQL Query

Am going to create two temporary tables named participant and employee tables.Participant code is related between these two tables.In employee table bulk score update need to be done.so let's start the query.

Create table #tempParticipant
(
 ParticipantCode int,
 ParticipantName varchar(max),
 ParticipantScore int,
 CreatedDate datetime,
 Deleted bit
)
GO

insert into #tempParticipant(ParticipantCode, ParticipantName , ParticipantScore , CreatedDate ,
 Deleted)
 values
  (1,'Anand',5,GETDATE(),1),
  (2,'Arun',1,GETDATE(),0),    
  (3,'Siva',3,GETDATE(),0),
  (4,'Hari',1,GETDATE(),0),
  (1,'Anand',7,GETDATE(),0),
GO 

Create table #tempEmployee
(
 EmployeeCodeCode int,
 ParticipantCode int,
 EmployeeScore int,
 CreatedDate datetime
)
GO

insert into #tempEmployee(EmployeeCodeCode, ParticipantCode , EmployeeScore , CreatedDate )
 values
  (100,1,0,GETDATE()),
  (102,2,0,GETDATE()),
  (103,3,0,GETDATE()),
  (104,4,0,GETDATE())
GO

--before bulk update
select EmployeeCodeCode, ParticipantCode , EmployeeScore  from #tempEmployee

--Here is the stuff
update A set A.EmployeeScore=B.ParticipantScore 
from 
#tempEmployee A left join #tempParticipant B 
on A.ParticipantCode=B.ParticipantCode and B.Deleted=0

--after bulk update
select EmployeeCodeCode, ParticipantCode , EmployeeScore  from #tempEmployee

Output
        In this post i tried to update bulk columns in SQL server table. My previous post Javascript PAN Number validation in Asp.net Textbox onkeypress event, SQL Query to find Nth Highest or Lowest salary of an employee table.