473,888 Members | 2,208 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2814
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*********@ho tmail.comwrote in message
news:11******** ************@n6 0g2000hse.googl egroups.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...@nospa m-
online.sbcgloba l.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...@ho tmail.comwrote in message

news:11******** ************@n6 0g2000hse.googl egroups.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_i d, a.primary_table _id,
b.foreign_colum n_id, b.primary_colum n_id, c.column_id
from SYS.SYSFOREIGNK EY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table _id = b.foreign_table _id AND
a.foreign_key_i d = b.foreign_key_i d
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_i d, a.primary_table _id,
c.table_name, b.foreign_colum n_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table _id AND column_id = b.foreign_colum n_id),
b.primary_colum n_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table _id AND column_id = b.primary_colum n_id)
from SYS.SYSFOREIGNK EY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table _id = b.foreign_table _id AND
a.foreign_key_i d = b.foreign_key_i d
JOIN SYS.SYSTABLE c ON
a.primary_table _id = c.table_id
where a.foreign_table _id=XXX

Jul 10 '07 #7
cobolman (ol*********@ho tmail.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****@sommarsk og.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

20
3934
by: de Beers | last post by:
mysql_query("DELETE FROM cart WHERE ItemId=$ItemId"); There is the code but the result in my databse is that the ID number changes from, lets say, 77 to 78 with 78's contents being empty. Therefore when I look at the results - the deleted ID77 is gone but now I have ID78 with no content! Does anyone know why and how do I make it stop? MIchael
5
2864
by: Mike | last post by:
I am not a sql person and could use some help with a delete...here is what I want: I have the following tables/fields (only including necessary fields) answers result_id results result_id
14
3622
by: php newbie | last post by:
I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values. DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 = y.col_2) Error msg: The table 'TableA' is ambiguous. Can this be done with SQL or should I use T-SQL with cursors here?
1
3854
by: Nimmi Srivastav | last post by:
There's a rather nondescript book called "Using Borland C++" by Lee and Mark Atkinson (Que Corporation) which presents an excellent discussion of overloaded new and delete operators. In fact there are quite a few things that I learned that I did not know before. For example, while I knew that the new and delete operators can be overloaded for classes, I did not know that that the global new and delete operators can also be overloaded. ...
15
2399
by: Roy Smith | last post by:
I understand that "delete xp" deletes a scalar object and "delete xp" deletes an array of objects, but what I don't understand is why you need to tell the compiler which you're doing. When you do "delete xp", the delete procedure (not sure if that's the right terminology) obviously knows how many objects were allocated by the corresponding "new" call. So, why can't it just know whether you did "new x" or "new x"?
6
1879
by: Jay Nabonne | last post by:
Hi, This might sound odd, but we want to replace the allocation scheme used by new and delete without changing operator new and operator delete. (The global operators are shared and we can't change them.) We can replace operator new functionality by providing additional parameters to the new function call (ala Stroustrup): class foo_t;
1
3898
by: Douglas Peterson | last post by:
class Allocator { public: virtual void * Alloc(size_t) = 0; virtual void * Free(void*) = 0; }; class Object { public:
1
5718
by: Swaq | last post by:
Or, at least, I find it complicated :-) Hopefully it is not that hard but here goes: I have two tables: Persons and Relations Persons contain personal data such as person_ID, name, adresse, etc. and a special-status-code (a person with this code set = 1 is a VIP)
8
3084
by: starman7 | last post by:
i have a table with objects in categories and their positions. there will be several rows with category 400, and they will have various positions, i want to delete only the row with the lowest position. i can select the row i want to delete, but don't know how to delete just this row. here's my select:
15
5047
by: LuB | last post by:
I am constantly creating and destroying a singular object used within a class I wrote. To save a bit of time, I am considering using 'placement new'. I guess we could also debate this decision - but for the sake of this post ... I'm searching for an answer that assumes said decision. If I allocate memory in the class declaration: char buffer;
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11173
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5810
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4635
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4239
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.