Delete on table

  • Hi,

     

    I have a table with two columns.The structure is as shown below :

     

    Name     City

    A            B

    A           B

    A           B

    A           B

    I have no primary key. And I want to delete 3 rows out of 4.

    How can i delete them.

    PLs help.

    Thanks.

  • You can't. You can only delete a record if you can identify it. (At least in sQL 2000. In SQL 2005 the DELETE TOP 3 ... might work.)

    Your best bet would be to add an identity column to the table and use that to do the delete. You can always drop the identity column later if you don't want it.

    Are you trying to clean duplicate data out of a table?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you just want to clean up the data, you can put this data into a temp table that has an identity column in it, then delete the data and then truncate the main table and port the data back...something like this:

    CREATE TABLE NO_KEY (NAME VARCHAR(5), CITY VARCHAR(5))

    GO

    INSERT INTO NO_KEY VALUES ('A', 'B')

    INSERT INTO NO_KEY VALUES ('A', 'B')

    INSERT INTO NO_KEY VALUES ('A', 'B')

    INSERT INTO NO_KEY VALUES ('A', 'B')

    INSERT INTO NO_KEY VALUES ('X', 'Y')

    INSERT INTO NO_KEY VALUES ('X', 'Y')

    GO

    SELECT IDENTITY(INT, 1, 1) AS ROW_NUM, * INTO #TEMP FROM NO_KEY

    DELETE FROM #TEMP

    WHERE EXISTS (SELECT 1 FROM #TEMP X

      WHERE X.NAME = #TEMP.NAME

      AND X.CITY = #TEMP.CITY

      AND X.ROW_NUM < #TEMP.ROW_NUM)

    DELETE NO_KEY

    GO

    INSERT INTO NO_KEY (NAME, CITY) SELECT NAME, CITY FROM #TEMP

    GO

    SELECT * FROM NO_KEY

    --Output

    NAME  CITY 

    ----- -----

    A     B

    X     Y

    (2 row(s) affected)

    And once the data has been cleaned up, proceed with adding a PK to this table - like the above post said, add a surrogate key using identity.

  • Gr8 !!!

    Thank you for ur help.

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

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