How to solve this Sql Query

Posted by Jayakumars under C# on 9/21/2016 | Points: 10 | Views : 367 | Status : [Member] [MVP] | Replies : 3
Hi

How to update Duplicate Records only . i mention condition below.


Create Table emp1
(
Id int primary key identity(1,1),
Empname varchar(50),
EmpID int,
Empstatus int,
empcreateDate Datetime
)


insert into emp1 values('John',1,1,GETDATE())
insert into emp1 values('John',1,1,GETDATE())
insert into emp1 values('John',1,1,GETDATE())
insert into emp1 values('John wesly',2,1,GETDATE())
insert into emp1 values('John wesly',2,1,GETDATE())
insert into emp1 values('Johnsob',3,1,GETDATE())
insert into emp1 values('Johnsobwer',4,0,GETDATE())
insert into emp1 values('Felix',5,1,GETDATE())
insert into emp1 values('Felix',5,1,GETDATE())
insert into emp1 values('Felix',5,1,GETDATE())
insert into emp1 values('Felix',5,1,GETDATE())
insert into emp1 values('Felix',5,1,GETDATE())
insert into emp1 values('Felix',5,0,GETDATE())
insert into emp1 values('Felix',5,0,GETDATE())


SELECT * FROM EMP1


-- For Ex
-- How to Update empstatus =0 last Effect Record but same empid based on Date

1 John 1 1 2016-09-21 15:16:03.340
2 John 1 1 2016-09-21 15:16:10.167
3 John 1 1 2016-09-21 15:22:35.773

-- I need output this

1 John 1 1 2016-09-21 15:16:03.340
2 John 1 1 2016-09-21 15:16:10.167
3 John 1 0 2016-09-21 15:22:35.773

Mark as Answer if its helpful to you


Responses

Posted by: Arjundt on: 9/22/2016 [Member] Starter | Points: 25

Up
0
Down
update EMP1 set Empstatus=0 where Id=(select top 1 Id from EMP1 where EmpID=1 order by Id desc)


Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 9/22/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

i mention one records so you can mention empid=1 but i have lot of records in my table around 5000 records so how we can update
particular record update duplicate record suppose each empid 10 or 12 records but i need update without 1 record.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 9/23/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Dear Jayakumar,

We can do the entire query for you (as we did earlier also), however, we would like you to give a try with the hint that we will provide.

We will show you the technique of getting the duplicates

;With CTE AS(
Select Rn=Row_Number() Over (Order by EmpName), e.*
From emp1
)

Select *
From CTE
Where Rn >1


This query will get you the duplicate records.

Now apply the Update command

Hope you will be able to go ahead.

All the best. (:

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response