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
7  2787 
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" <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.
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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. ...
|
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
|
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.
...
|
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...
|
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...
| | |
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...
|
by: Douglas Peterson |
last post by:
class Allocator
{
public:
virtual void * Alloc(size_t) = 0;
virtual void * Free(void*) = 0;
};
class Object
{
public:
|
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,...
|
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...
|
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 -...
|
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,...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
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...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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 ...
| |
