Post reply

How to delete a row with the internal row number?

  • July 27, 2007 at 11:07 am

    #197118

    Hi

    We have duplicate data in a table which doesn't have a key. With a StoredProc we can group the data, find out which rows having > 1 and store them in a temp table. Now we could loop thru and get the real duplicated rows by a cursor from each entry in the temp table. Is there a way to delete then a row with its internal row number? Or may there is a more common way to solve this?

    Thanks for help

    Jan

     

  • July 27, 2007 at 11:38 am

    #722990

    Maybe I'm not understanding you right... Why don't you add some sort of primary key to the table and then delete?

    i.e.

    DELETE x

    FROM mytable x

    JOIN mytable y

    ON x.column1 = y.column1

    AND x.column2 = y.column2

    AND (... all your duplicate columns...)

    AND x.newPKcolumn > y.newPKcolumn

  • July 27, 2007 at 11:58 am

    #723000

    Or another solution, just for the kicks of it.

     

    Select valid data into newtable

    truncate oldtable

    drop old table

    rename new table

     

    you can change the drop rename by and insert if you want to keep all the constraints, triggers and indexes alive on the current table.

Viewing 3 posts - 1 through 2 (of 2 total)

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