Duplicate records

I have duplicate records where the duplicate data spans multiple
columns. I'm not sure how to delete the record I don't.
I have a table where the duplicate row is the opposite of the original:
Column 1 Column 2 Column 3 Column 4 Column 5
(Original) Data 1 Data 2 Data 3 Data 4 Data 5
(Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
(Original) Data 1 Data 2 Data 6 Data 7 Data 8
(Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
I don't need the duplicate record.
Thanks in advance,
SteveWithout asking how on earth s situation like this came about, the way to
delete the dupes is to delete using a self-join, to find, for each record
with a dupe, the duplicate which needs t obe deleted
Delete D
From Table D Join Table T
On D.Column1 = T.Column5
And D.Column2 = T.Column4
And D.Column3 = T.Column3
"stevem12@.hotmail.com" wrote:

> I have duplicate records where the duplicate data spans multiple
> columns. I'm not sure how to delete the record I don't.
>
> I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
> (Original) Data 1 Data 2 Data 3 Data 4 Data 5
> (Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
> (Original) Data 1 Data 2 Data 6 Data 7 Data 8
> (Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
> I don't need the duplicate record.
>
> Thanks in advance,
> Steve
>|||will this work?
select distinct
column1 as youruniquevaluefield
from yourtable
union
select distinct
column2 as youruniquevaluefield
from yourtable
union
select distinct
column3 as youruniquevaluefield
from yourtable
union
etc.... you get my point?
stevem12@.hotmail.com wrote:
> I have duplicate records where the duplicate data spans multiple
> columns. I'm not sure how to delete the record I don't.
>
> I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
> (Original) Data 1 Data 2 Data 3 Data 4 Data 5
> (Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
> (Original) Data 1 Data 2 Data 6 Data 7 Data 8
> (Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
> I don't need the duplicate record.
>
> Thanks in advance,
> Steve
>|||Unfortunately this did not work. It deletes all records.
CBretana wrote:
> Without asking how on earth s situation like this came about, the
way to
> delete the dupes is to delete using a self-join, to find, for each
record
> with a dupe, the duplicate which needs t obe deleted
>
> Delete D
> From Table D Join Table T
> On D.Column1 = T.Column5
> And D.Column2 = T.Column4
> And D.Column3 = T.Column3
> "stevem12@.hotmail.com" wrote:
>
original:
Column 5
5
1
8
1|||On 22 Apr 2005 19:54:11 -0700, stevem12@.hotmail.com wrote:

>I have duplicate records where the duplicate data spans multiple
>columns. I'm not sure how to delete the record I don't.
Hi Steve,
I don't understand this part. Please post table structure and sample
data (as CREATE TABLE and INSERT statements) and required output (see
www.aspfaq.com/5006).

>I have a table where the duplicate row is the opposite of the original:
>
> Column 1 Column 2 Column 3 Column 4 Column 5
>(Original) Data 1 Data 2 Data 3 Data 4 Data 5
>(Duplicate) Data 5 Data 4 Data 3 Data 2 Data 1
>(Original) Data 1 Data 2 Data 6 Data 7 Data 8
>(Duplicate) Data 8 Data 7 Data 6 Data 2 Data 1
>
>I don't need the duplicate record.
Try if this works:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS d
-- Recognise duplicates
WHERE d.Column1 = MyTable.Column5
AND d.Column2 = MyTable.Column4
AND d.Column3 = MyTable.Column3
AND d.Column4 = MyTable.Column2
AND d.Column5 = MyTable.Column1
-- Delete only "lowest" (when ordered by Column1, Column2, ...)
AND ( d.Column1 > MyTable.Column1
OR (d.Column1 = MyTable.Column1 AND d.Column2 > MyTable.Column2)))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Take a bow Hugo! That did it!
Thank you very much.
Steve|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. My guess is that you meant sometime like this:
CREATE TABLE Foobar
(c1 INTEGER NOT NULL,
c2 INTEGER NOT NULL,
c3 INTEGER NOT NULL,
c4 INTEGER NOT NULL,
c5 INTEGER NOT NULL,
PRIMARY KEY (c1,c2,c3,c4,c5));
I am not sure how you define a duplicate versus an original. Are the
columns ordered in some way like (c1 < c2 < c3< c4 < c5)? Or will
either do? What about (1,1,1,1,1) and its mirror duplicate
(1,1,1,1,1), which blows up Hugo's solution?
If you do not have to preserve the column ordering, I would sort each
row then get rid of the duplicates with a SELECT DISTINCT.