How to update this situation Records [Resolved]

Posted by Jayakumars under C# on 10/7/2016 | Points: 10 | Views : 1149 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	@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

@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

Latest Posts