How to update this situation Records [Resolved]

Posted by Jayakumars under C# on 10/7/2016 | Points: 10 | Views : 313 | Status : [Member] [MVP] | Replies : 2
How to update duplicate record only one

create table tiff1
(
id int primary key identity(1,1),
refid int,
refstatus int,
refdttm datetime
)
select * from Test.dbo.tiff1


-- I need update duplicate record but only latest datetime records only 1 records only need update for status
-- here 1001 like this

1	1001	1	2016-10-07 15:05:02.100
2 1001 1 2016-10-07 15:05:04.577
3 1001 1 2016-10-07 15:05:05.790


--here i need update like this output
--
1	1001	1	2016-10-07 15:05:02.100
2 1001 1 2016-10-07 15:05:04.577
3 1001 0 2016-10-07 15:05:05.790


-- for all duplicate records but my table rows more then 5000 thousand
--different differnet refid how to fixed this

Mark as Answer if its helpful to you


Responses

Posted by: Manicse on: 10/7/2016 [Member] Bronze | Points: 50

Up
0
Down

Resolved
Hi,

We can use this following code to get the requirement.



UPDATE Table1
SET Column1 = column1 +'a'
WHERE exists(
select row
from (
SELECT
Column1 ,
Row_Number() over(Partition by Column1 order by Column1) as row
FROM Clients
) as subquery
where subquery.row = 2
)


Mani.R

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

Posted by: Rajnilari2015 on: 10/10/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Jayakumars Sir please try this,

--create the table
create table #tiff1
(
id int primary key identity(1,1),
refid int,
refstatus int,
refdttm datetime
)

--Insert some rows to it

INSERT INTO #tiff1 VALUES(1001,1,'2016-10-07 15:05:02.100')
INSERT INTO #tiff1 VALUES(1001,1,'2016-10-07 15:05:04.577')
INSERT INTO #tiff1 VALUES(1001,1,'2016-10-07 15:05:05.790')

INSERT INTO #tiff1 VALUES(1002,1,'2016-10-07 15:05:02.103')
INSERT INTO #tiff1 VALUES(1002,1,'2016-10-07 15:05:03.580')
INSERT INTO #tiff1 VALUES(1002,1,'2016-10-07 15:05:05.793')


--Select recordsin each refid sorted by refdttm
;WITH CTE AS(
SELECT
Rn=Row_Number() OVER(PARTITION BY refid ORDER BY refdttm DESC)
,*
FROM #tiff1)

--The update statement
UPDATE t
SET t.refstatus = 0
FROM #tiff1 t
INNER JOIN CTE c
ON t.id = c.id
WHERE c.Rn=1 --Filter by Latest time

--Finally Project the record
SELECT *
FROM #tiff1

--Clean Up
DROP TABLE #tiff1


Result
---------

id	refid	refstatus	refdttm
1 1001 1 2016-10-07 15:05:02.100
2 1001 1 2016-10-07 15:05:04.577
3 1001 0 2016-10-07 15:05:05.790
4 1002 1 2016-10-07 15:05:02.103
5 1002 1 2016-10-07 15:05:03.580
6 1002 0 2016-10-07 15:05:05.793



--
Thanks & Regards,
RNA Team

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

Login to post response