locked
how to delete duplicated records in sqlserver RRS feed

  • Question

  • User836525179 posted

    Greetings to all
    Dear friends, I need to delete the records in the database table that have a specific duplicate field, but I don't know how to write the appropriate query for this operation. Please help us in this regard.

    form example my table is like this : 

    1    amir      6496   12

    2    ali          1235    11

    3    amir       4564    16

    4     golo       1321    18

    In this query it is necessary to show me the result of the table below

    1    amir      6496   12

    2    ali          1235    11

    4     golo       1321    18

    It doesn't matter which record is deleted, only the record with the duplicate field is deleted

    Saturday, April 25, 2020 5:49 PM

All replies

  • User1535942433 posted

    Hi  uniservice3,

    Accroding to your description,I suggest you could try distinct().

     var query = (from r in table1 select r).distinct();

    More details,you could refer to below article:

    https://forums.asp.net/t/1828064.aspx?LINQ+select+distinct+c+

    Best regards,

    Yijing Sun

    Sunday, April 26, 2020 7:49 AM
  • User-857013053 posted

    You can try this way to solve your problem

    with cte as (

    select 

    column_name1,

    column_name2,

    column_name3,

    column_name4,

    ROW_NUMBER() OVER (

    PARTITION BY

             column_name1,

             column_name2,

             column_name3,

             column_name4

    ORDER BY

             column_name1,

             column_name2,

            column_name3,

            column_name4

    ) row_num

    FROM

       table_name

    )

    DELETE FROM cte

    where row_num > 1;

    Monday, April 27, 2020 5:52 AM