Post reply

delete query

  • July 3, 2009 at 3:47 am

    #135866

    create table temp(id int,path varchar)

    insert into temp(1,'C:\my\')

    insert into temp(2,'C:\my\')

    insert into temp(3,'C:\my\')

    insert into temp(4,'C:\my\')

    insert into temp(5,'C:\my\')

    I want to delete all records where path ='C:\my\'

    but want to keep any of one record from this.

  • July 3, 2009 at 3:52 am

    #1019692

    Just to clarify , do you want to delete all but one of these rows ?


    Clear Sky SQL
    My Blog[/url]

  • July 3, 2009 at 3:56 am

    #1019693

    Try this,

    This will delete all records where path ='C:\my\'

    delete temp where path ='C:\my\' and id (select min(id) from temp where path ='C:\my\' )

  • July 3, 2009 at 3:57 am

    #1019694

    If 2005 +

    create table temp(id int,path varchar(1024))

    go

    insert into temp values(1,'C:\my\')

    insert into temp values(2,'C:\my\')

    insert into temp values(3,'C:\my\')

    insert into temp values(4,'C:\my\')

    insert into temp values(5,'C:\my\')

    go

    with cteRow(id,path,Rownum)

    as

    (

    Select id,path,row_number() over(partition by path order by id)

    from temp

    )

    delete from cteRow

    where Path = 'C:\my\'

    and Rownum 1

    select * from temp


    Clear Sky SQL
    My Blog[/url]

  • July 3, 2009 at 5:11 am

    #1019711

    Hope this query may help you

    DELETE TOP (SELECT COUNT(*)-1 FROM temp WHERE path='C:\my\' ) FROM temp

  • July 3, 2009 at 6:26 am

    #1019740

    Maybe it could be usefull to do a select distinct into a new separate table.

    Then you could use 'truncate table' and actually rename to the new table to the old one.

    But otherwise i'll go for the row_number approach.

    Greets.

  • July 3, 2009 at 6:39 am

    #1019748

    nidhisharma01 (7/3/2009)

    Hope this query may help you

    DELETE TOP (SELECT COUNT(*)-1 FROM temp WHERE path='C:\my\' ) FROM temp

    No... that will just delete the first n rows, regardless of the path.

    DELETE TOP (SELECT COUNT(*)-1 FROM temp WHERE path='C:\my\' ) FROM temp WHERE path='C:\my\'

  • July 3, 2009 at 6:43 am

    #1019750

    my mistake,

    you are right!

    I forgot to add where clause in the main query. This fix would serve the purpose.

    Thanks!

  • July 4, 2009 at 12:00 am

    #1019905

    Thanks All,

    It works

  • July 4, 2009 at 12:10 am

    #1019907

    DELETE f

    FROM (

    SELECT ROW_NUMBER() OVER (PARTITION BY Path ORDER BY ID) AS recID

    FROM Table1

    ) AS f

    WHERE recID > 1

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply