Posted by bill.bertovich on 06/28/07 21:39

On Jun 28, 4:50 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > Actually I want to delete all rows which is duplicate on NAME and
> > TYPE.
>
> You can remove the TAG criteria from the original statement I posted so that
> all of the rows with duplicate NAME and TYPE values are removed:
>
> CREATE TABLE dbo.PMTOOLS
> (
> [NAME] varchar(30) NOT NULL,
> [TYPE] varchar(30) NOT NULL,
> [TAG] varchar(30) NOT NULL
> )
> GO
>
> INSERT INTO dbo.PMTOOLS
> SELECT 'TEST1', '12', 'A'
> UNION ALL SELECT 'TEST1', '12', 'B'
> UNION ALL SELECT 'TEST2', '12', 'A'
> UNION ALL SELECT 'TEST4', '14', 'B'
> GO
>
> DELETE dbo.PMTOOLS
> FROM dbo.PMTOOLS
> JOIN (
> SELECT NAME, TYPE
> FROM dbo.PMTOOLS
> GROUP BY NAME, TYPE
> HAVING COUNT(*) > 1
> ) AS dups
> ON
> dups.NAME = PMTOOLS.NAME AND
> dups.TYPE = PMTOOLS.TYPE
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "cobolman" <olafbrun...@hotmail.com> wrote in message
>
> news:1183028881.884401.9280@n60g2000hse.googlegroups.com...
>
> > Actually I want to delete all rows which is duplicate on NAME and
> > TYPE.
>
> > Name Type Tag
> > ---------------------------------
> > TEST1 12 A
> > TEST1 12 B
> > TEST2 12 A
> > TEST4 14 B
>
> > If you take this example, I'd like to delete TEST1 and only have TEST2
> > and TEST4 left in my table.
>
> > This is a temporary table used to compare tables in different
> > databases.
> > I move all the tables from both databases into this temp table, and to
> > find the tables that are found only in on of the databases, I want to
> > perform the deletion as mentioned above.
>
> > The result should give me the tables (occurence) that is missing in
> > one of the databases. The TAG tells me which.

cobolman,

I may be reading more into this than I should, but I am assuming you
want to keep one row for each set of dups. Dan's script will remove
all occurrences of the dup rows.

Do you have a sequential unique ID, or timestamp type of column on the
table? Let us know the details (schema) if you do and I'll post a
solution for you.

-- Bill

[Back to original message]