Category: ASP.NET

07 Oct 2016 15:11

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	5

How to update this situation Records

Hi

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

#768040

07 Oct 2016 16:12

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	2

Hi,

Can we try this with CTE, Just gave a try to below code


;WITH cte AS
(
  SELECT
      ROW_NUMBER() OVER(PARTITION BY refid ORDER BY refdttm) AS rno,
      id
  FROM tiff1
)

UPDATE cte SET Status='0' WHERE  rno=1

Thanks,
Mani

#768405

29 Nov 2016 11:50

Joined:	17/11/2016
Level:	Bronze
Points:	19

Points:	2

try to below code

;WITH cte AS
(
SELECT Top(1) * from tiff1 order by refdttm desc
)

UPDATE cte SET Status='0' WHERE rno=1

Software Developer
iFour Technolab Pvt. Ltd.

#768407

29 Nov 2016 14:25

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	3

Hi,
Try this:

1. This will retrieve records from db with refid = 1 in order with latest date.


SELECT  Top 1 refdttm FROM       tiff1
WHERE  refid = 1 
ORDER  BY refdttm desc

2. Update refstatus = 0 for above retrieved record.


UPDATE tiff1 SET refstatus  = 0 WHERE 
refid = 1 And refdttm = (SELECT Top 1 refdttm FROM  tiff1 
						 WHERE  refid = 1 
						 ORDER  BY refdttm desc)

How to update this situation Records

About dotnetspider.com

Quick Links

General

Services