delete of mirrored rows

[Jeff Kish]

hi.
I've seen ways to delete duplicate rows.
Can someone give me some sql to do this?

I have a table with varchar table_name_start, varchar column_name,
varchar table_name_end;

it has rows like this:
table1 col1 table2
table1 col2 table 3
table2 col1 table1

I'd lke to delete the rows if they exist with the names swapped
around, i.e. like above since the first and third share a column name
and the table_name_start/end matches the others table_name_end/start,
I'd like to delete one and leave the other.

I'm scratching my head trying to figure this out.

thanks

[David Portas]

What is the key of this table? Please post proper DDL so that we don't
have to guess. I'm going to assume that the combination of all three
columns is unique, in which case try this:

DELETE FROM tbl
WHERE EXISTS
(SELECT *
FROM tbl AS T
WHERE T.table_name_start = tbl.table_name_end
AND T.table_name_end = tbl.table_name_start
AND T.column_name = T.column_name)
AND table_name_start > table_name_end ;

--
David Portas
SQL Server MVP
--

[Jeff Kish]

On 6 Oct 2005 01:00:14 -0700, "David Portas"
<RE****************************@acm.org> wrote:

What is the key of this table? Please post proper DDL so that we don't
have to guess. I'm going to assume that the combination of all three
columns is unique, in which case try this:

DELETE FROM tbl
WHERE EXISTS
(SELECT *
FROM tbl AS T
WHERE T.table_name_start = tbl.table_name_end
AND T.table_name_end = tbl.table_name_start
AND T.column_name = T.column_name)
AND table_name_start > table_name_end ;

--
David Portas
SQL Server MVP

David,
Very sorry but I did not realize that would make a difference. (really
I didn't).
There actually was not a primary key (this was a temporary working
table).
I've put a primary key into place now, but I used the script before
that.
It seemed to work fine I've never used the 'AS' before.. I guess I
better study up a bit on it.
Oh.. the ddl was this:
CREATE TABLE [allEdges] (
[table_name_start] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[column_name] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[table_name_end] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

[David Portas]

In this case the key makes a difference to the extent that my DELETE
statement will delete "mirrored" rows but won't delete all duplicates
(all three columns identical). For that you would need a key, a cursor
or an intermediate table. Also, my DELETE won't remove rows with NULLs,
which I can see may be an issue now that you've posted DDL with
nullable columns.

In any case, it makes sense to include keys with your DDL or to state
that your table doesn't have a key. Keys and constraints can make a big
difference to the solution.

--
David Portas
SQL Server MVP
--

[Jeff Kish]

On 6 Oct 2005 03:53:10 -0700, "David Portas"
<RE****************************@acm.org> wrote:

In this case the key makes a difference to the extent that my DELETE
statement will delete "mirrored" rows but won't delete all duplicates
(all three columns identical). For that you would need a key, a cursor
or an intermediate table. Also, my DELETE won't remove rows with NULLs,
which I can see may be an issue now that you've posted DDL with
nullable columns.

In any case, it makes sense to include keys with your DDL or to state
that your table doesn't have a key. Keys and constraints can make a big
difference to the solution.

--
David Portas
SQL Server MVP

I understand completely. Thanks for the edifications.
(that's what we're here for, eh?)
Cheers

[Alexander Kuznetsov]

another way of removing duplicates is to
select distinct * into new table
drop old table
rename the new table

Could be much faster

[Jeff Kish]

On 6 Oct 2005 11:32:52 -0700, "Alexander Kuznetsov"
<AK************@hotmail.COM> wrote:

another way of removing duplicates is to
select distinct * into new table
drop old table
rename the new table

Could be much faster

I'm not sure because they are not exact duplicates.. just mirrors