How can I remove a duplicate row?

I have a table that has two rows in it that are identical and need to
delete one of them. Since any kind of where clause in a delete
statement will hit both of them, how can I remove one of these rows?
TIA
Pick the dups by grouping and put them in a temp table. remove all
duplicate records from the parent table and insert the groped
duplicates (in this table, you should have one occourance of each
record) from the temp table into this old table that dosent have any
rows of these duplicate records.
Hope I didnt loose you with my wording.. ..! (It would help if you had
given some DDL code...!
|||http://www.aspfaq.com/2431
Now how about having a key and/or other constraints to prevent this from
happening again?
http://www.aspfaq.com/2509
http://www.aspfaq.com/
(Reverse address to reply.)
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com...
> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA
|||On 24 Jan 2005 11:08:44 -0800, "QueryBuilder" <pg.242w@.gmail.com>
wrote:

>Pick the dups by grouping and put them in a temp table. remove all
>duplicate records from the parent table and insert the groped
>duplicates (in this table, you should have one occourance of each
>record) from the temp table into this old table that dosent have any
>rows of these duplicate records.
>Hope I didnt loose you with my wording.. ..! (It would help if you had
>given some DDL code...!
This makes sense. I was hoping I could remove the extra in place. (My
thinking is that if the DB knows there are two rows there must be
someway that it uniquely identifies them.)
|||Matthew Speed <mspeed@.mspeed.net> wrote in
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com:

> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA
set rowcount 1
delete from t1
where col1=1 and col2=1
|||On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>http://www.aspfaq.com/2431
>Now how about having a key and/or other constraints to prevent this from
>happening again?
The keys are now in place. This was a client database I was hired to
do some work on. When I saw the problem my first thought was to add
constraints but one can't implement a unique constraint unless the
existing data is already unique.

>http://www.aspfaq.com/2509
|||"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:cjmav0dg4s0uki574285s1rekljfod4lfa@.4ax.com... [vbcol=seagreen]
> On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
> <ten.xoc@.dnartreb.noraa> wrote:
from
> The keys are now in place. This was a client database I was hired
to
> do some work on. When I saw the problem my first thought was to add
> constraints but one can't implement a unique constraint unless the
> existing data is already unique.
I add identitiy columns to tables all the time to get rid of dups. You
can leave the identitiy column around or simply remove it after you
are done. Another technique is to create a new table as select
distinct * from xxx, drop the old one and rename the new one as
appropriately.
|||> I add identitiy columns to tables all the time to get rid of dups.
With sensible design, you won't have dupes at all. Does your data not have
a candidate key? Do you not use primary keys for any reason?
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVkkXtmAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> With sensible design, you won't have dupes at all. Does your data
not have
> a candidate key? Do you not use primary keys for any reason?
Data is often loaded in a database before being transformed and
duplicates removed. This is what happens in the real world.
|||> Data is often loaded in a database before being transformed and
> duplicates removed. This is what happens in the real world.
The real world? What is that? Glad my job is inside a vacuum, where we
load data into a staging table, insert the NEW, NON-DUPLICATE rows into our
production tables, then blow away or archive the staging data. (Rather than
just throwing everything into our production tables, and removing duplicates
there.)