How to solve this Sql Query

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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 Kumaraspcode2009@gmail.com 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

0	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

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

Latest Posts