469,579 Members | 1,098 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

How to delete rows in a table when no primary key is defined

Hello,

I want to delete duplicate rows in a table when no primary key is
defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one row.

I tried deleting self joins and exists operator. But it is deleting
both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520
Jul 23 '05 #1
11 9034
Add a identity column to the table and delete the row with de min value.

Jul 23 '05 #2
Patarroxa wrote:
Add a identity column to the table and delete the row with de min
value.


Or copy the data with a SELECT DISTINCT into another table, drop the
original and rename the new table.

robert

Jul 23 '05 #3
Create a new table (with a key), then use SELECT DISTINCT or GROUP BY to
populate it from the old one.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #4
"suma" wrote:
Hello,

I want to delete duplicate rows in a table when no primary key
is defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one
row.

I tried deleting self joins and exists operator. But it is
deleting both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma


Thanks for the response.
But it has to be done using a single sql statement.
Using multiple we can do it...is there any way to do using a single
sql statement.
Thanks,
Suma
Jul 23 '05 #5
First of all this is not a table by definition. A table must have a
key. And the answer is No, it will take more than one statement to
clean up the base table -- either a cursor, an IDENTITY or a SELECT
DISTINCT. You can put the SELECT DISTINCT into a VIEW as a kludge.

You did fire the guy that did this, didn't you?

Jul 23 '05 #6
Using a single DELETE statement it can't be done if there is no way to
differentiate between the rows. That's why a primary key is supposed to
be mandatory. Why should you have a table without a key?

--
David Portas
SQL Server MVP
--

Jul 23 '05 #7
You're not really saving anything doing it this way over the alternatives,
and it's *very* slow for large numbers of duplicates.

SET ROWCOUNT=1

DELETE table1
WHERE EXISTS (SELECT *
FROM table1 AS t2
WHERE table1.col1 = t2.col1 and table1.col2 = t2.col2 and table1.col3 =
t2.col3
GROUP BY t2.col1, t2.col2, t2.col3
HAVING COUNT(*) > 1)

WHILE @@ROWCOUNT>0
DELETE ... --same statement all over

Disclaimer: This is not tested. I am not responsible for any loss of data
incurred by use of this technique. SELECT INTO with GROUP BY is probably
safest and fastest, as recommended by others.

Also see books online, Index, DELETE (described), and the description of
DELETE FROM table WHERE CURRENT OF cursor_name
"suma" <Do********@dbForumz.com> wrote in message
news:4_***************************************@dbf orumz.com...
"suma" wrote:
Hello,

I want to delete duplicate rows in a table when no primary key
is defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one
row.

I tried deleting self joins and exists operator. But it is
deleting both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma


Thanks for the response.
But it has to be done using a single sql statement.
Using multiple we can do it...is there any way to do using a single
sql statement.
Thanks,
Suma


Jul 23 '05 #8
And thats why oracle we can delete the duplicate rows using rowid or rownum
and not in sql server. Some unique identity has to be there !!

Jul 23 '05 #9
True, but with correct design you'll never need to. The problem IS
soluble in SQL Server too, it's just that SQL Server requires that you
fix things rather than allow you to live with such a kludgy solution.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #10
>> Oracle we can delete the duplicate rows using rowid or rownum and
not in sql server <<

Yes, Oracle is a sequential file system and a piss-poor RDBMS under the
covers. Parallelism, set processing, and all the other things that
allow a good SQL implmentation to run 4 to 5 orders of magnitude faster
and 80-90% smaller are not available in Oracle and cannot be because of
a horrible architecture. Look up the performance for Nucleus (Sand
Technology) and other VLDB products.

Jul 23 '05 #11
There is a way to do this, but it will take massive amounts of time to
delete many rows, because the duplicate rows are deleted one row at a
time. It goes like this:

SET ROWCOUNT 1
-- Generate a rowcount > 0
SELECT COUNT(*) FROM MyTable
While @@rowcount > 0
Begin
DELETE MyTable
WHERE (
SELECT COUNT(*)
FROM MyTable T1
WHERE T1.Col1 = MyTable.Col1
AND T2.Col2 = MyTable.Col2
) > 1
End
-- don't forget this line!
SET ROWCOUNT 0
Hope this helps,
Gert-Jan

suma wrote:

Hello,

I want to delete duplicate rows in a table when no primary key is
defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one row.

I tried deleting self joins and exists operator. But it is deleting
both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

Jul 23 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
13 posts views Thread by =?Utf-8?B?VmVybm9uIFBlcHBlcnM=?= | last post: by
3 posts views Thread by =?Utf-8?B?S2F5xLFoYW4=?= | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.