You must Sign In to post a response.
  • Category: ASP.NET

    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
    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
    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
    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)


Sign In to post your comments