How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
Jim
DELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim
|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?
|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||You told that the rows are about THE SAME, you didn′t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:
> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü�meyer" wrote:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
> > There are two columns in the table1,
> >
> > ID|AccountName | ContactName |
> > 1 | ebay.com | Alex
> > 2 | ebay.com |
> >
> >
> > Look the second row, it is reduplicate info which is absolutely same with
> > the first row. So I'd like to delete the second row. How to use SQL to do
> > that?
> >
> > Cheers,
> > Jim|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn´t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches t
he
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
>|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn′t mentioned something o
f
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

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-Dis...pict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520Add a identity column to the table and delete the row with de min value.|||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|||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
--|||"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|||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?|||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
--|||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" <DoNotEmail@.dbForumz.com> wrote in message
news:4_761872_6b47fee83970ff4272fca067cae7180d@.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|||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 !!|||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
--|||>> 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.|||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-Dis...pict221110.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

How to delete a big amount of data

Hi, suppose i have 3 tables

main table

CREATE TABLE table1 (
[t1ID] [int] primary key ,
[t1Name] nvatchar(50) NOT NULL,
.....................

)

and connections tables (t1ID is a foreign key in all connection tables)

CREATE TABLE tbl2 (
[t1ID] [int] not null,
[gID] [int] not null
PRIMARY KEY (t1ID, gID)
)

CREATE TABLE tbl3 (
[t1ID] [int] not null,
[pID] [int] not null
PRIMARY KEY (t1ID, pID)
)

************************************************** *****

t1ID CLISTERED INDEX
in othre tables each primary key is clustered.

gID and pID are keys from other table: gtable, ptable

Now in case one row was deletes from table1 i need to delete all t1ID's
from the connection tables (each of connection may have e.g. 200000 rows).
If u use delete on cascade it's take a lot of time, i want to delete let's
say 1000 rows till all irrelevant rows will be deleted.

How To accomplish this??
Thanks.

--
Message posted via http://www.sqlmonster.comakej via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> Now in case one row was deletes from table1 i need to delete all t1ID's
> from the connection tables (each of connection may have e.g. 200000 rows).
> If u use delete on cascade it's take a lot of time, i want to delete let's
> say 1000 rows till all irrelevant rows will be deleted.

Well, if you have FK constraints from the connections tables to the
main table, you cannot delete the connection from that table until all
references from the connection tables are gone. So while you could batch
things like:

SET ROWCOUNT 10000
WHILE EXISTS (SELECT * FROM tbl2 WHERE tl1D = @.id_to_delete)
DELETE tbl2 WHERE tlID = @.id_to_delete
SET ROWCOUNT 0

I can't really see that it will help you.

You could of course drop the foreign-key constraint, and the start some
background process that deletes the rows in the other table, but that's
a pretty wild thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, your suggestion is helpful however how can implement it,
i guess by job that must define, so when this job will be start and who
will start it??

Also i can remove all these relationship, only the ID's will be stay, now
each time when t1ID will be deleted i will put this t1ID in some pemanent
table suppose [toDelete] and from now i need to start job or jobs that
let's say every 10 minut will remove 1000 rows from each connection table
or from first connection table after this from second and so on, in the end
i need to remove this t1ID. However i'm new in sql and i don't really know
how to implement it. Maybe u reject this and have more efficient suggestion.

Thanks

--
Message posted via http://www.sqlmonster.com|||Any ideas ???

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Thanks Erland, your suggestion is helpful however how can implement it,
> i guess by job that must define, so when this job will be start and who
> will start it??
> Also i can remove all these relationship, only the ID's will be stay,
> now each time when t1ID will be deleted i will put this t1ID in some
> pemanent table suppose [toDelete] and from now i need to start job or
> jobs that let's say every 10 minut will remove 1000 rows from each
> connection table or from first connection table after this from second
> and so on, in the end i need to remove this t1ID. However i'm new in sql
> and i don't really know how to implement it. Maybe u reject this and
> have more efficient suggestion.

Well, since I don't like dropping foreign-key constraints, I would first
look into the situation a little closer. It seems that you have a
"connection" and you have 200000 rows added for that connection that
you want to drop. Maybe there is reason to consider why all those rows
were added in the first place?

This may be a stupid question, but pleaes keep in mind that since I know
nothing about your business, I have to try some stabs in the dark.

But if you truly want an asynchronous delete, I would set up a in SQL
Agent that runs with some frequency and which deletes orphaned rows.
There could be a trigger on the table, so that when a row is deleted,
the job is started, but frankly, I would actually skip that step. This
job could be devised so that it deletes all orphans it can find, possibly
batched with SET ROWCOUNT. Or it could be written so that it stops after
some time, and take remaining oprhans on the next time, depending on
how you want to spread the load.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp