I have a table with the following columns,
NAME, TYPE, TAG
And there may be 'duplicates' on name and type.
How can I delete them??
I want to delete all with duplicate NAME and TYPE
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
>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"
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
>
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.
> 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"
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.
>
>
>
On Jun 28, 4:50 am, "Dan Guzman"
> > 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"
>
> 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
Bill,
I do want to remove all occurences of the dup rows.
The result set should only hold the ones that did not have any dups.
Thanks to both (Dan and Bill) :)
I guess I need help on another one as well, ...
I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).
This SQL gives me what I want :
Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
b.foreign_column_id, b.primary_column_id, c.column_id
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
where a.foreign_table_id= XXX
But, .. what I'd really like is to instead of the column_id's and
table_id's have the actual name. I can get this from systable and
syscolumn, but I'm not sure how to write the sql
Hmm...could this be it?
Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
c.table_name, b.foreign_column_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table_id AND column_id = b.foreign_column_id),
b.primary_column_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table_id AND column_id = b.primary_column_id)
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
JOIN SYS.SYSTABLE c ON
a.primary_table_id = c.table_id
where a.foreign_table_id=XXX
cobolman (olafbrungot@hotmail.com) writes:
> I guess I need help on another one as well, ...
>
> I'd like to do a select to find all the foreign keys of a given table,
> and the foreign_key columns.. (Sybase).
You are probably better off asking in comp.databases.sybase. It does not
seem from your queries that neither Sybase use their old system
tables anymore.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx