Re: DELETE where syntax ... need help :)

I want create a delete statement of this select result:

SELECT A.NAME, A.TYPE, A.TAG FROM PMTOOLS A
WHERE EXISTS (SELECT B.NAME, B.TYPE, B.DATABASE FROM PMTOOLS B WHERE
A.TAG != B.TAG)
ORDER BY A.NAME

Are you absolutely certain that you want to delete all the rows returned by this select statement? It looks to me like this would delete all the rows in your table (if you have more that one distinct TAG value). Note that a column list in an EXISTS subquery is always ignored so the only effective criteria is the "A.TAG != B.TAG" correlation.

My guess from your posts is that you have multiple rows with the same NAME and TYPE values and want to delete all but one. This requires a primary key or other unique identifier in order to identify the one you want to keep. If the combination of NAME, TYPE and TAG is unique, you can do something like:

DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE, MIN(TAG) AS TAG
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) > 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE AND
dups.TAG < PMTOOLS.TAG


If this isn't what you need, please post DDL (CREATE TABLE), sample data (INSERT statements) and expected results.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"cobolman" <olafbrungot@xxxxxxxxxxx> wrote in message news:1183025012.775192.49530@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I want create a delete statement of this select result:

SELECT A.NAME, A.TYPE, A.TAG FROM PMTOOLS A
WHERE EXISTS (SELECT B.NAME, B.TYPE, B.DATABASE FROM PMTOOLS B WHERE
A.TAG != B.TAG)
ORDER BY A.NAME


.