Saturday, January 17, 2015

Bulk update in sql server table columns using joins

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

2 comments:

  1. recently i came your blog and have been read along it was interesting it's very helpful to me.Hyderabadsys offering DOT NET ONLINE TRAINING by most experience faculty.

    ReplyDelete
  2. I have read your blog its very attractive and impressive. I like it your blog.

    Dot Net Training in Chennai Dot Net Training in Chennai .Net Online Training .Net Online Training Dot Net Training in Chennai Dot Net Training in Chennai


    Dot Net Online Training Dot Net Online Training LINQ Online Training LINQ Online Training ASP.NET Online Training ASP.NET Online Training

    ReplyDelete

Followers