help deleting SIMILAR records (not duplicate)

i've read lots of usenet and and microsoft support articles about how
to remove duplicate rows from a table, but i am trying to modify that
logic to delete "similar" rows. for example, consider the following:
create table t1 (
col1 int,
col2 bit,
col3 bit)
insert into t1 values (1, 0, 0)
insert into t1 values (2, 0, 0)
insert into t1 values (3, 0, 1)
now, clearly there are no duplicate rows. but what if, for the sake of
logical consistency, i need to remove "similar" rows, defining similar
in this example as rows with duplicate col2 and col3 values? keep in
mind: i don't care which row gets deleted (deleting the col1 value of 1
OR 2 will be fine)
most of the examples i'm reading involve selecting distinct * into a
temp table, which won't work for me, because the whole row is not
duplicated.
the result i am looking for AFTER the similar row deletion is as
follows:
select * from t1
col1 col2 col3
-- -- --
1 0 0
3 0 1
thanks for any help!Delete Table
Where Col1 In(
Select Min(T1.Col1)
From Table1 As T1
Group By T1.Col2, T1.Col3
Having Count(*) > 1
)
Thomas
"jason" <iaesun@.yahoo.com> wrote in message
news:1114800493.591486.311530@.l41g2000cwc.googlegroups.com...
> i've read lots of usenet and and microsoft support articles about how
> to remove duplicate rows from a table, but i am trying to modify that
> logic to delete "similar" rows. for example, consider the following:
> create table t1 (
> col1 int,
> col2 bit,
> col3 bit)
> insert into t1 values (1, 0, 0)
> insert into t1 values (2, 0, 0)
> insert into t1 values (3, 0, 1)
> now, clearly there are no duplicate rows. but what if, for the sake of
> logical consistency, i need to remove "similar" rows, defining similar
> in this example as rows with duplicate col2 and col3 values? keep in
> mind: i don't care which row gets deleted (deleting the col1 value of 1
> OR 2 will be fine)
> most of the examples i'm reading involve selecting distinct * into a
> temp table, which won't work for me, because the whole row is not
> duplicated.
> the result i am looking for AFTER the similar row deletion is as
> follows:
> select * from t1
> col1 col2 col3
> -- -- --
> 1 0 0
> 3 0 1
> thanks for any help!
>|||this is exactly the kind of logic i need, even though that will only
delete 1 similar row, where as i would like to only KEEP 1 similar row.
your code satisfies the example completely, however i might actually
have dozens of "similar" rows, for which i would only want to keep the
rows identified by the select min(col1) statement.
thanks again!|||You could run the query several times in succession or put it in a loop :)
BTW, once you get this resolved, you might want to make sure that the
client-side logic doesn't allow inserts of "similar rows"; or you could
ensure this via trigger.
"jason" <iaesun@.yahoo.com> wrote in message
news:1114801761.830867.51880@.z14g2000cwz.googlegroups.com...
> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||Try,
delete t1
where exists(select * from t1 as t2 where t1col2 = t2.col2 and t1.col3 =
t2.col3 and t2.col1 > t1.col1)
AMB
"jason" wrote:

> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||DELETE FROM T1
WHERE EXISTS
(SELECT *
FROM T1 AS T2
WHERE T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col1 > T2.col1)
David Portas
SQL Server MVP
--

Help - Error deleting publication or adding subscription

In the haste of trying to delete a publication/subscription combo, it looks
as if it's stuck in limbo. I must have done something out of sequence.
In Microsoft SQL Server Management Studio it appears the subscription on the
secondary server has been deleted yet I can't delete the publication as it
says it can't delete the subscription. When I try to add the subscription on
the second server it says it already exists, even though It doesn't list
itself under subscriptions.
I'm trying to remove all replication and I'm wondering if it needs to be
deleted manually through the database as something is out of sync.
Thanks
Ron
If this is a push subscription issue the following
sp_dropsubscription @.publication= 'PublicationName', @.article= 'ALL',
@.subscriber= 'SubscriberName'
, @.destination_db= 'SubscriptionDatabase', @.ignore_distributor = 0If this
does not clear it up do thissp_dropsubscription @.publication=
'PublicationName', @.article= 'ALL', @.subscriber= 'SubscriberName'
, @.destination_db= 'SubscriptionDatabase', @.ignore_distributor = 1Then
check your subscription database. See if there are entries in the tables
MSreplication_subscriptions and MSsubscription_agents for this publication
and delete the entries if they exist.-- http://www.zetainteractive.com -
Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:AB12955C-2B1C-4C1A-9D11-7F47D8FB6A29@.microsoft.com...
> In the haste of trying to delete a publication/subscription combo, it
> looks
> as if it's stuck in limbo. I must have done something out of sequence.
> In Microsoft SQL Server Management Studio it appears the subscription on
> the
> secondary server has been deleted yet I can't delete the publication as it
> says it can't delete the subscription. When I try to add the subscription
> on
> the second server it says it already exists, even though It doesn't list
> itself under subscriptions.
> I'm trying to remove all replication and I'm wondering if it needs to be
> deleted manually through the database as something is out of sync.
> Thanks
> Ron
|||Thanks Hillary,
Before i saw your reply I executed the following and it worked:
EXEC sp_droppublication @.publication = N'xxxxxxx'
go
USE master
GO
EXEC sp_replicationdboption @.dbname = N'xxxxxxx',
@.optname = N'publish', @.value = N'false'
"Hilary Cotter" wrote:

> If this is a push subscription issue the following
> sp_dropsubscription @.publication= 'PublicationName', @.article= 'ALL',
> @.subscriber= 'SubscriberName'
> , @.destination_db= 'SubscriptionDatabase', @.ignore_distributor = 0If this
> does not clear it up do thissp_dropsubscription @.publication=
> 'PublicationName', @.article= 'ALL', @.subscriber= 'SubscriberName'
> , @.destination_db= 'SubscriptionDatabase', @.ignore_distributor = 1Then
> check your subscription database. See if there are entries in the tables
> MSreplication_subscriptions and MSsubscription_agents for this publication
> and delete the entries if they exist.-- http://www.zetainteractive.com -
> Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:AB12955C-2B1C-4C1A-9D11-7F47D8FB6A29@.microsoft.com...
>
>

Help - deleting existing auto-generated primary key

hi guys,

just a question regarding database design

i have a table with an auto-generated primary key but the problem is this:

say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.

how do i remedy this?

thanx

Two ways:

1. Use TRUNCATE instead of delete if you are deleting *all* rows

2. Use "DBCC CHECKIDENT(TABLE_NAME_HERE, RESEED, 0)" to reset the current identiy value back to required position if you want to use "DELETE" instead of "TRUNCATE"

|||

Another common practice is to create some sort of an ID column as well as the auto-generated primary key. Sounds like this way work better for you in this case if you plan on frequently deleting, inserting, etc.

Thanks,
Sam Lester (MSFT)

|||

hi,

wanna ask.is TRUNCATE only used if i want to delete all records in a table?

how am i able to do this if i only want to delete a selected row and all rows? say i got 4 records and i only want to delete the 2nd record.

so will the 3rd record be numbered as 2 now or will it remain as 3?