Fragmented array: delete

Showing posts with label delete. Show all posts

Thursday, March 29, 2012

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>sql

Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:
> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.|||I use sp_help tablename to get the name and the columns in an index.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegroups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Monday, March 26, 2012

Finding and Deleting Duplicate Records

I have a table that has many duplicate records and I need to delete all but
one of the duplicate records. Is there a way I can identify these records
with a sql statement?
Example record:
(Comment_id, site_id, date_entered, user_id, comment)
6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
non-franchise signage.Another thing to consider. The comment field is a text data type.
"Mike Collins" wrote:
> I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Mike,
Depending on the size of the comment field you could do something like this:
SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
FROM <TABLENAME>
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Thanks, but how would I delete all the other records that I don't want?
"Jerry Spivey" wrote:
> Mike,
> Depending on the size of the comment field you could do something like this:
> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> FROM <TABLENAME>
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >I have a table that has many duplicate records and I need to delete all but
> > one of the duplicate records. Is there a way I can identify these records
> > with a sql statement?
> >
> > Example record:
> > (Comment_id, site_id, date_entered, user_id, comment)
> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
> >
> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
>
>|||Mike,
Can you post the DDL for the table(s) so I don't have to guess about the
keys?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> Thanks, but how would I delete all the other records that I don't want?
> "Jerry Spivey" wrote:
>> Mike,
>> Depending on the size of the comment field you could do something like
>> this:
>> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> FROM <TABLENAME>
>> HTH
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >I have a table that has many duplicate records and I need to delete all
>> >but
>> > one of the duplicate records. Is there a way I can identify these
>> > records
>> > with a sql statement?
>> >
>> > Example record:
>> > (Comment_id, site_id, date_entered, user_id, comment)
>> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>> >
>> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>>|||CREATE TABLE [dbo].[StatusRptComments] (
[Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
[Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
(
[Comment_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
"Jerry Spivey" wrote:
> Mike,
> Can you post the DDL for the table(s) so I don't have to guess about the
> keys?
> Thanks
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> > Thanks, but how would I delete all the other records that I don't want?
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Depending on the size of the comment field you could do something like
> >> this:
> >>
> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> FROM <TABLENAME>
> >>
> >> HTH
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >I have a table that has many duplicate records and I need to delete all
> >> >but
> >> > one of the duplicate records. Is there a way I can identify these
> >> > records
> >> > with a sql statement?
> >> >
> >> > Example record:
> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >> >
> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >>
> >>
> >>
>
>|||Mike,
What criteria do you want for the DELETE? I.e., ok the comment column is
the same for x records but the other columns may be different i.e,
comment_id, dateentered, etc... Which one record do you want to retain?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> CREATE TABLE [dbo].[StatusRptComments] (
> [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> (
> [Comment_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> "Jerry Spivey" wrote:
>> Mike,
>> Can you post the DDL for the table(s) so I don't have to guess about the
>> keys?
>> Thanks
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> > Thanks, but how would I delete all the other records that I don't want?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Depending on the size of the comment field you could do something like
>> >> this:
>> >>
>> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> FROM <TABLENAME>
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >I have a table that has many duplicate records and I need to delete
>> >> >all
>> >> >but
>> >> > one of the duplicate records. Is there a way I can identify these
>> >> > records
>> >> > with a sql statement?
>> >> >
>> >> > Example record:
>> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >> >
>> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >>
>> >>
>> >>
>>|||Sorry...I want to keep the entry with the latest date. Thanks for your
patience with my not providing complete details up front.
"Jerry Spivey" wrote:
> Mike,
> What criteria do you want for the DELETE? I.e., ok the comment column is
> the same for x records but the other columns may be different i.e,
> comment_id, dateentered, etc... Which one record do you want to retain?
> Thanks
> Jerry
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> > CREATE TABLE [dbo].[StatusRptComments] (
> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> > (
> > [Comment_id]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Can you post the DDL for the table(s) so I don't have to guess about the
> >> keys?
> >>
> >> Thanks
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> > Thanks, but how would I delete all the other records that I don't want?
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Depending on the size of the comment field you could do something like
> >> >> this:
> >> >>
> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> FROM <TABLENAME>
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >I have a table that has many duplicate records and I need to delete
> >> >> >all
> >> >> >but
> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> > records
> >> >> > with a sql statement?
> >> >> >
> >> >> > Example record:
> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >> >
> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Mike,
Try:
BEGIN TRAN
DELETE #STATUSRPTCOMMENTS
FROM #STATUSRPTCOMMENTS S2
WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
WHERE S1.SITE_NO = S2.SITE_NO)
--ROLLBACK TRAN
--OR
--COMMIT TRAN
My mind is shot today so use this explicit tran and see if it works if not
roll back and tell me what is wrong and I'll look at it this weekend.
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> Sorry...I want to keep the entry with the latest date. Thanks for your
> patience with my not providing complete details up front.
> "Jerry Spivey" wrote:
>> Mike,
>> What criteria do you want for the DELETE? I.e., ok the comment column is
>> the same for x records but the other columns may be different i.e,
>> comment_id, dateentered, etc... Which one record do you want to retain?
>> Thanks
>> Jerry
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
>> > CREATE TABLE [dbo].[StatusRptComments] (
>> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
>> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ,
>> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
>> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
>> > (
>> > [Comment_id]
>> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> > GO
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Can you post the DDL for the table(s) so I don't have to guess about
>> >> the
>> >> keys?
>> >>
>> >> Thanks
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> >> > Thanks, but how would I delete all the other records that I don't
>> >> > want?
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Mike,
>> >> >>
>> >> >> Depending on the size of the comment field you could do something
>> >> >> like
>> >> >> this:
>> >> >>
>> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> >> FROM <TABLENAME>
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >> >I have a table that has many duplicate records and I need to
>> >> >> >delete
>> >> >> >all
>> >> >> >but
>> >> >> > one of the duplicate records. Is there a way I can identify these
>> >> >> > records
>> >> >> > with a sql statement?
>> >> >> >
>> >> >> > Example record:
>> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >> >
>> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||That works great...thanks Jerry.
"Jerry Spivey" wrote:
> Mike,
> Try:
> BEGIN TRAN
> DELETE #STATUSRPTCOMMENTS
> FROM #STATUSRPTCOMMENTS S2
> WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
> AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
> WHERE S1.SITE_NO = S2.SITE_NO)
> --ROLLBACK TRAN
> --OR
> --COMMIT TRAN
> My mind is shot today so use this explicit tran and see if it works if not
> roll back and tell me what is wrong and I'll look at it this weekend.
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> > Sorry...I want to keep the entry with the latest date. Thanks for your
> > patience with my not providing complete details up front.
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> What criteria do you want for the DELETE? I.e., ok the comment column is
> >> the same for x records but the other columns may be different i.e,
> >> comment_id, dateentered, etc... Which one record do you want to retain?
> >>
> >> Thanks
> >>
> >> Jerry
> >>
> >>
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> >> > CREATE TABLE [dbo].[StatusRptComments] (
> >> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> >> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ,
> >> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> >> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> >> > (
> >> > [Comment_id]
> >> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> >> > GO
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Can you post the DDL for the table(s) so I don't have to guess about
> >> >> the
> >> >> keys?
> >> >>
> >> >> Thanks
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> >> > Thanks, but how would I delete all the other records that I don't
> >> >> > want?
> >> >> >
> >> >> > "Jerry Spivey" wrote:
> >> >> >
> >> >> >> Mike,
> >> >> >>
> >> >> >> Depending on the size of the comment field you could do something
> >> >> >> like
> >> >> >> this:
> >> >> >>
> >> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> >> FROM <TABLENAME>
> >> >> >>
> >> >> >> HTH
> >> >> >>
> >> >> >> Jerry
> >> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >> >I have a table that has many duplicate records and I need to
> >> >> >> >delete
> >> >> >> >all
> >> >> >> >but
> >> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> >> > records
> >> >> >> > with a sql statement?
> >> >> >> >
> >> >> >> > Example record:
> >> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >> >
> >> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Finding and Deleting Duplicate Records

Friday, March 9, 2012

find out a user/account permission...

is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > is there an easy to find out an user/account's permsions within a
> database?
> > thank you
> > a list of objects and rights.
> > such as
> >
> > select delete insert exec
> > table1 x x
> > table2 x
> > stored proc x
> >
> >
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> > The attached query should work for you, it is based around sysprotects.
> >
> > --
> > Mark Allison, SQL Server MVP
> > http://www.markallison.co.uk
> >
> >
> >
> >
> > "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> > news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> > > is there an easy to find out an user/account's permsions within a
> > database?
> > > thank you
> > > a list of objects and rights.
> > > such as
> > >
> > > select delete insert exec
> > > table1 x x
> > > table2 x
> > > stored proc x
> > >
> > >
> >
> >
> >
>

find out a user/account permission...

is there an easy to find out an user/account's permsions within a database?
thank you
a list of objects and rights.
such as
select delete insert exec
table1 x x
table2 x
stored proc xSteve,
Refer 'sp_helprotect' in BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> is there an easy to find out an user/account's permsions within a
database?
> thank you
> a list of objects and rights.
> such as
> select delete insert exec
> table1 x x
> table2 x
> stored proc x
>|||can you resend the query w/o using attachement?
my company remove any email attachment from outside automatically. (or if
you can rename the file and change the file extension to .port and resend
it?)
thank you
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
> The attached query should work for you, it is based around sysprotects.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
>
>
> "Steve Lin" <lins@.nospam.portptld.com> wrote in message
> news:edcL0R$JEHA.428@.TK2MSFTNGP11.phx.gbl...
> database?
>
>|||What follows -- thanks to Mark -- is the script he attached...
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@.REMOVETHISTEXTmetromilwaukee
.com
URL http://www.metromilwaukee.com/clintongallagher/

"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>|||I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Steve Lin" <lins@.nospam.portptld.com> wrote in message
news:uW9ZfuHKEHA.2808@.TK2MSFTNGP11.phx.gbl...
> can you resend the query w/o using attachement?
> my company remove any email attachment from outside automatically. (or if
> you can rename the file and change the file extension to .port and resend
> it?)
> thank you
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:uR$LaZFKEHA.2784@.TK2MSFTNGP10.phx.gbl...
>

Sunday, February 26, 2012

Find and delete reversed key pairs.

Hi!

I have a table with only two columns, which contain some city codes in the form of AAA, BBB. What I need to do is to find reversed key pairs in the same table and get rid of them so only one pair stays. In example, I have:

AAA, BBB

CCC, DDD

BBB, AAA

EEE, FFF

FFF, EEE

DDD, CCC

AAA, BBB and BBB, AAA mean the same to me, so I would like to leave only one of them. Do you have any idea on how to achieve this? I tried to use a cursor (yeah, I know cursors are bad, but this is a task I will most likely run once or every few weeks), but the table has over 130k records and it looks like I need at least a few hours for the cursor to complete it's work. Here's what I did:

Code Snippet

declare
@.origCity char(3)
,@.destCity char(3)

declare crs_cities cursor local fast_forward for
select
origCity, destCity
from
dbo.airports
order by
origCity

open crs_cities

fetch next from crs_cities
into @.origCity, @.destCity

while @.@.fetch_status = 0
begin
-- Delete rows having the same city pair in reverse order.
delete from
dbo.airports
where
destCity = @.origCity
and
origCity = @.destCity

fetch next from crs_cities
into @.origCity, @.destCity
end

close crs_cities
deallocate crs_cities

I could also add an autoincrement column before the pairs and use a while loop instead of a cursor, but there must be some SQL query to do it the proper way. It may be a whole procedure, whatever.

Thanks.

delete from airports
where origCity>destCity
and exists (select * from airports a2
where a2.origCity=airports.destCity
and a2.destCity=airports.origCity)
|||You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.|||

I cannot believe it was so simple! x_x

If I may ask - why origCity>destCity and not something else. I don't get it.

Kent Waldrop 2007 Mar wrote:
You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.

This was some data imported from a CSV I got from a client, so it's not a case. Wink

|||

pawel.krakowiak wrote:

If I may ask - why origCity>destCity and not something else. I don't get it.

You need to ensure that the operation only deletes one record of each pair (without that condition it would have deleted both records of the pair). The condition arbitrarily selects the records where origCity sorts after destCity to remove. Mark could equally well have used origCity < destCity and removed the other record of the pair.

|||Thought so... I never used such construct (I mean, it depends on the context) before, though. Good to know.

Thursday, March 29, 2012

Finding Indexes for a table

Finding Indexes for a table

Finding Indexes for a table

Monday, March 26, 2012

Finding and Deleting Duplicate Records

Finding and Deleting Duplicate Records

Finding and Deleting Duplicate Records

Friday, March 9, 2012

find out a user/account permission...

find out a user/account permission...

Sunday, February 26, 2012

Find and delete reversed key pairs.

Fragmented array

Blog Archive

About Me