Regarding delete a record

HI I am having a table which does not have any primary key or unique key.

How can I delete the duplicate开发者_开发技巧 records?

Can any one of u tell me?

The easiest way would be to copy all of the duplicates into another identical table, delete them all from the original table, then put back the duplicates (just once for each unique one of course) from the temporary table.

For example:

BEGIN TRANSACTION

CREATE TABLE Holding_Table (my_string VARCHAR(20) NOT NULL)

INSERT INTO Holding_Table (my_string)
SELECT my_string
FROM My_Table
GROUP BY my_string
HAVING COUNT(*) > 1

DELETE MT
FROM Holding_Table HT
INNER JOIN My_Table MT ON MT.my_string = HT.my_string

INSERT INTO My_Table (my_string)
SELECT my_string
FROM Holding_Table

DROP TABLE Holding_Table

COMMIT TRANSACTION

This is just a simple example with one column. You would need to adjust it for your table obviously. Then be sure to add a primary key to your table...

You would have to create a primary key first. Then you would be able to run an aggregate query and see how many duplicates there are and delete based off of the new ID. You could then remove the primary key and make another field the primary key if you so desired (or stick with the one you created).

I have done this many times when fixing ancient legacy databases.

If you use: SET ROWCOUNT 1

You can get SQL to delete only a single row, and use whatever technique you prefer to delete the identical rows one at a time.

To revert back to normal behaviour, use: SET ROWCOUNT 0

However, it would be advisable to at least add a column that allows you to uniquely identify each row so that you can avoid this problem in future. The following does the trick:

ALTER TABLE TableName ADD TableName_ID int IDENTITY NOT NULL

Now you can simply: DELETE TableName WHERE TableName_ID = ? for each of your duplicates.

Check this site on support.microsoft.com: Site

It can tell you alot of how to identify, etc.

Adding this as another answer since it's a different approach...

You could also add a new column to the table, make that one unique, and then use that to delete all but one of the duplicate rows. For example:

ALTER TABLE My_Table
ADD my_id INT IDENTITY NOT NULL

DELETE
     MT1
FROM
     My_Table MT1
WHERE EXISTS (
          SELECT
               *
          FROM
               My_Table MT2
          WHERE
               MT2.my_string = MT1.my_string AND
               MT2.my_id < MT1.my_id)

ALTER TABLE My_Table
DROP COLUMN my_id