Deleting semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

Deleting more duplicates

Hello,
I have a stored procedure that deletes duplicates
in one table:

....
AS
BEGIN
DELETE FROM mytable
WHERE Id IN(
SELECT Max(id)
from mytable
group by date, idsens
having count(*) >1
)
END

sometimes it happens that I have >2 rows with duplicated values.
How can I write a new stored procedure that delete all rows with
duplicated infomrations (leaving only one row with those values)?

Thanks
M.A.On Tue, 25 Jul 2006 10:36:10 GMT, Maury wrote:

Quote:

Originally Posted by

>Hello,
>I have a stored procedure that deletes duplicates
>in one table:
>
>...
>AS
>BEGIN
>DELETE FROM mytable
>WHERE Id IN(
>SELECT Max(id)
>from mytable
>group by date, idsens
>having count(*) >1
>)
>END
>
>sometimes it happens that I have >2 rows with duplicated values.
>How can I write a new stored procedure that delete all rows with
>duplicated infomrations (leaving only one row with those values)?

Hi Maury,

For SQL Server 2005:

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

For all versions of SQL Server:

DELETE FROM mytable
WHERE EXISTS
(SELECT *
FROM mytable AS m2
WHERE m2.date = mytable.date
AND m2.idsens = mytable.idsens
AND m2.Id < mytable.Id);

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis ha scritto:

Quote:

Originally Posted by

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn 1;

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?
thanks!!!!
M.A.|||Maury (maurizio.alberti_TOGLI_@.gmail.com) writes:

Quote:

Originally Posted by

This is very very interesting (I didn't know these commands)
I have to search a good reference
or stored procedure language manual
for sql server 2005
some hints?

Books Online. Ships with SQL Server. Update available from the link below.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

deleting duplicates

I'm having trouble deleting duplicates from enterprise manager. I get "Key
column information is insufficient or incorrect. Too many rows were affected
by update." messages.
I can run delete queries through query analyzer but that will delete the
original reocrd and duplicate. The table in question has 3 columns (all 3
columns show dup data) with no indexes and about 15 original rows duplicated.
Question 1: Why can't I delete from enterprise manager? The help file is
useless.
Question 2: What is the T-SQL for deleting the duplicate record (but leaving
the original)?
On Wed, 29 Mar 2006 20:01:01 -0800, carl wrote:
Hi Carl,
I'll address your questions in reverse order.

>Question 2: What is the T-SQL for deleting the duplicate record (but leaving
>the original)?
There is none (but see below form some kludges).
In the DELETE statement, you use a WHERE clause to tell SQL Server which
row(s) to delete. If two or more rows have the exact same data in ALL
columns, then any WHERE clause that matches one will match the other as
well. That's but one of the reasons why each table should always have at
least one PRIMARY KEY or UNIQUE constraint.

>Question 1: Why can't I delete from enterprise manager? The help file is
>useless.
Since Enterprise Manager is just a fancy front end that translates your
mouse clicks to queries, it has the same limitation as you have when
writing T-SQL statements in Query Analyzer.

>I can run delete queries through query analyzer but that will delete the
>original reocrd and duplicate. The table in question has 3 columns (all 3
>columns show dup data) with no indexes and about 15 original rows duplicated.
To delete just a single duplicated row, you can use this kludge:
SET ROWCOUNT 1
DELETE FROM MyTable
WHERE Column1 = ...
AND Column2 = ...
....
SET ROWCOUNT 0
If you want to get rid of *ALL* duplicates, rename the table, then
recreate it (don't forget to add the constraints this time!!) and move
the data back, using DISTINCT to squish the dups:
sp_rename 'MyTable', 'MyTableTMP', 'OBJECT'
go
CREATE TABLE MyTable
(Column1 int NOT NULL,
...
PRIMARY KEY (Column1, Column2)
)
go
INSERT INTO MyTable (Column1, ...)
SELECT DISTINCT Column1, ...
FROM MyTableTMP
go
DROP TABLE MyTableTMP
go
Hugo Kornelis, SQL Server MVP

Deleting duplicates

I have a table with 5 columns.
Column 1 is the ID and is unique
Column 2 is a number and has many duplicates
Column 3-5 are just desicrptions
It holds 60,000 products but many man of them are duplicates, the way I know
is that they have the same code in column # 2
How can I select only non-duplicates ?
Column 1: ID
Column 2: SKU
Column3: Description
Column 4: Price
Column5: Quantity
I need to select all columns, that's why I could not : select distinct SKU
from table, because it would only select one column, how can
I select all columns where sku is unique ?
ASELECT id, sku, description, price, quantity
FROM YourTable AS T
WHERE id =
(SELECT MIN(id)
FROM YourTable
WHERE sku = T.sku)
David Portas
SQL Server MVP
--|||Excelent !
Thanks David
A
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112881987.619604.173390@.z14g2000cwz.googlegroups.com...
> SELECT id, sku, description, price, quantity
> FROM YourTable AS T
> WHERE id =
> (SELECT MIN(id)
> FROM YourTable
> WHERE sku = T.sku)
> --
> David Portas
> SQL Server MVP
> --
>sql

Deleting duplicate rows from a table without using temporary table

Hi all ,
Can anyone tell me the query to delete the duplicates from a table
CASE_COUNTRY
Country CountryCode
-- --
France FR
France FR
Italy IT
Germany GM
Here France is a duplicate row , i have 10000 records like this
Regards
AlertAdminAlertAdmin wrote:
> Hi all ,
> Can anyone tell me the query to delete the duplicates from a table
> CASE_COUNTRY
> Country CountryCode
> -- --
> France FR
> France FR
> Italy IT
> Germany GM
>
> Here France is a duplicate row , i have 10000 records like this
>
> Regards
> AlertAdmin
You could try the following. I'm not sure what advantage if any that
this would have over using a temp table.
BEGIN TRAN
GO
CREATE TRIGGER trg_case_country ON case_country
FOR DELETE
AS
INSERT INTO case_country (country, countrycode)
SELECT DISTINCT country, countrycode
FROM deleted ;
GO
DELETE FROM case_country ;
DROP TRIGGER trg_case_country;
COMMIT TRAN
GO
The most important step comes next. Add the missing keys:
ALTER TABLE case_country
ADD CONSTRAINT pk_case_country PRIMARY KEY (countrycode);
ALTER TABLE case_country
ADD CONSTRAINT ak1_case_country UNIQUE (country);
Your choice of country code for Germany is unusual. I suggest you use
the ISO standard country codes unless you are working to some unique
standard that requires otherwise:
http://www.iso.ch/iso/en/prods-serv...ex.html

David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||AlertAdmin wrote:
> Hi all ,
> Can anyone tell me the query to delete the duplicates from a table
> CASE_COUNTRY
> Country CountryCode
> -- --
> France FR
> France FR
> Italy IT
> Germany GM
>
> Here France is a duplicate row , i have 10000 records like this
>
> Regards
> AlertAdmin
In SQL Server 2005 the following also works for me, although I'm not
certain that this behaviour is well-documented or totally reliable.
CREATE TABLE case_country (country VARCHAR(35) NOT NULL, countrycode
CHAR(2) NOT NULL /* NO KEY! */);
INSERT INTO case_country VALUES ('France', 'FR');
INSERT INTO case_country VALUES ('France', 'FR');
INSERT INTO case_country VALUES ('Italy', 'IT');
INSERT INTO case_country VALUES ('Germany', 'GM');
WITH T AS
(SELECT country, countrycode, ROW_NUMBER()
OVER (PARTITION BY country, countrycode
ORDER BY country, countrycode) AS row_num
FROM case_country)
DELETE FROM T
WHERE row_num>1;
SELECT * FROM case_country;
Result:
country countrycode
-- --
France FR
Italy IT
Germany GM
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks alot , it works fine for me
Regards
AlertAdmin|||And don't forget to add the primary key and unique constraint!
Hope this helps.
Dan Guzman
SQL Server MVP
"AlertAdmin" <sajin@.iprlab.com> wrote in message
news:1141472981.407005.316810@.z34g2000cwc.googlegroups.com...
> Thanks alot , it works fine for me
> Regards
> AlertAdmin
>|||Hi,
I got another solution by adding a identity column
alter table case_country add test_iden int identity(1,1)
delete from case_country where test_iden not in (select min(test_iden)
from tab group by country)
alter table tab drop column test_iden
Regards
AlertAdmin

Deleting Duplicate Records

I have a table tmPunchtimeSummary which contains a sum of employee's hours
per day. The table contains some duplicates.

code:

CREATE TABLE [tmPunchtimeSummary]
(
[iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
[sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dTotalHrs] [decimal](18, 4) NULL
) ON [PRIMARY]
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1234', 4.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2468', 8.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '1357', 9.0)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5)
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
VALUES('20060610', '2345', 8.5

How can I write a delete statement to only delete the duplicates which in
this case would be the 1st and 5th records?
Thanks,
Ninel
Message posted via http://www.webservertalk.comDELETE FROM tmPunchtimeSummary
WHERE EXISTS
(select * from tmPunchtimeSummary as X
where tmPunchtimeSummary.sCalldate = X.sCalldate
and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
and tmPunchtimeSummary.iTmPunchTimeSummaryId <
X.iTmPunchTimeSummaryId)
Roy Harvey
Beacon Falls, CT
On Wed, 14 Jun 2006 16:41:01 GMT, "ngorbunov via webservertalk.com"
<
u9125@.uwe>
wrote:

>
I have a table tmPunchtimeSummary which contains a sum of employee's hours
>
per day. The table contains some duplicates.
>
>

code:

>
CREATE TABLE [tmPunchtimeSummary]
>
(
>
 [iTmPunchTimeSummaryId] [int] IDENTITY (1, 1) NOT NULL ,
>
 [sCalldate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [sEmployeeId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>
 [dTotalHrs] [decimal](18, 4) NULL
>
 ) ON [PRIMARY]
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1234', 4.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2468', 8.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '1357', 9.0)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5)
>
>
INSERT tmPunchtimeSummary (sCalldate, sEmployeeId, dTotalHrs)
>
VALUES('20060610', '2345', 8.5
>

>
>
How can I write a delete statement to only delete the duplicates which in
>
this case would be the 1st and 5th records?
>
>
Thanks,
>
Ninel|||Thank you very much.
Roy Harvey wrote:
>DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
> (select * from tmPunchtimeSummary as X
> where tmPunchtimeSummary.sCalldate = X.sCalldate
> and tmPunchtimeSummary.sEmployeeId = X.sEmployeeId
> and tmPunchtimeSummary.dTotalHrs = X.dTotalHrs
> and tmPunchtimeSummary.iTmPunchTimeSummaryId <
> X.iTmPunchTimeSummaryId)
>Roy Harvey
>Beacon Falls, CT
>
>[quoted text clipped - 32 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||> DELETE FROM tmPunchtimeSummary
> WHERE EXISTS
In my figuring on paper, Roy's solution works.
I also offer another suggetion: Microsoft Access has lots and lots of
wizards to do hard stuff like that. So if you have a copy of Access, let it
be your quick-and-dirty friend. You can use the Access wizard to buzz up
some stuff really fast, and then just paste the SQL code into QA, clean it
up a bit, and then run it.
But I will warn you: Access is not up to the task of production code.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?
Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/default...b;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there :) )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>sql

deleting duplicate record

in my table, I have several rows that are complete duplicates. Needless to
say, I can't open the table and just delete 1 row.
How do I delete the copies and not all?Take a look at this link.
I've used it in the past to remove thousands of duplicates (dont ask how
they got there )
Once you've gotten rid of the dups, whack some contraints on where
necessary.
http://support.microsoft.com/defaul...kb;en-us;139444
Immy
"Johnfli" <john@.ivhs.us> wrote in message
news:eXaiytCLGHA.1272@.TK2MSFTNGP10.phx.gbl...
> in my table, I have several rows that are complete duplicates. Needless
> to say, I can't open the table and just delete 1 row.
> How do I delete the copies and not all?
>
>

Deleteing Duplicates

Hi,
Does anyone have a useful way of deleting duplicates so that it leaves 1 in
the table and removes the other. Currently I use a #temp table but was just
wondering if there is something slicker.
Thanks
Steve LloydDepends, assuming you have a primary key:
DELETE FROM YourTable
WHERE EXISTS
(SELECT *
FROM YourTable AS T
WHERE col1 = YourTable.col1
AND col2 = YourTable.col2
AND ... etc
AND key_col < YourTable.key_col)
If you don't have a key at all then SELECT DISTINCT into a new table
and add the key... and don't create tables without keys in future!
David Portas
SQL Server MVP
--|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
How to Identify and Delete Duplicate SQL Server Records
http://www.sql-server-performance.c..._duplicates.asp
AMB
"Steve Lloyd" wrote:

> Hi,
> Does anyone have a useful way of deleting duplicates so that it leaves 1 i
n
> the table and removes the other. Currently I use a #temp table but was ju
st
> wondering if there is something slicker.
> Thanks
> Steve Lloyd
>
>

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 TYPEActually 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

Quote:

Originally Posted by

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" <olafbrungot@.hotmail.comwrote in message
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

Quote:

Originally Posted by

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:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...
>

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>

Quote:

Originally Posted by

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

>

Quote:

Originally Posted by

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx