|
|
Posted by Dan Guzman on 06/28/07 10:46
>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@hotmail.com> wrote in message
news:1183025012.775192.49530@m36g2000hse.googlegroups.com...
>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
>
Navigation:
[Reply to this message]
|