Posted by Dan Guzman on 06/28/07 11:50

> 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" <olafbrungot@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 (occurences) that is missing in
> one of the databases. The TAG tells me which.
>
>
>

[Back to original message]