469,945 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

DELETE where syntax ... need help :)

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

Jun 28 '07 #1
7 2676
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.

Jun 28 '07 #2
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" <ol*********@hotmail.comwrote in message
news:11********************@n60g2000hse.googlegrou ps.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.
Jun 28 '07 #3
On Jun 28, 4:50 am, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.netwrote:
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.comwrote in message

news:11********************@n60g2000hse.googlegrou ps.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

Jun 28 '07 #4
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) :)

Jun 29 '07 #5
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

Jul 10 '07 #6
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

Jul 10 '07 #7
cobolman (ol*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 10 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by de Beers | last post: by
5 posts views Thread by Mike | last post: by
14 posts views Thread by php newbie | last post: by
1 post views Thread by Nimmi Srivastav | last post: by
15 posts views Thread by Roy Smith | last post: by
6 posts views Thread by Jay Nabonne | last post: by
1 post views Thread by Douglas Peterson | last post: by
8 posts views Thread by starman7 | last post: by
15 posts views Thread by LuB | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.