How to delete rows when no primary key is defined

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to delete rows when no primary key is defined

Author

Topic

sumanp
Starting Member

1 Post

Posted - 2005-04-21 : 10:09:33

Hello all,

I have the following problem. If you have a table with no primary key defined and you want to delete the duplicate rows in the table, how do we achieve this?

We can select do self join of the table and filter out the duplicate entries.
But how do we delete them?

Thanks in advance,
Suma

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-21 : 10:28:38

quote:
Originally posted by sumanp

Hello all,

I have the following problem. If you have a table with no primary key defined and you want to delete the duplicate rows in the table, how do we achieve this?

We can select do self join of the table and filter out the duplicate entries.
But how do we delete them?

Thanks in advance,
Suma

select the distinct values in to a temp table. Delete the duplicates from your original table by using the EXISTS operator. Something like :


delete yourTable
where exists (select 1 from yourTable as a
               where a.Col1 = yourTable.Col1
               and a.Col2 = yourTable.Col2
               and a.Col3 = yourTable.Col3)

then re-insert the distinct records in your temp table in to the main table.

Hope this helps.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources