June 21, 2005 at 2:58 pm
OK...for whatever reason (I am still investigating) my table PRODUCTION_TABLES has now 2 rows per each table on my production server. Let's say I figure out why this happened, but in the meantime need to delete the dupes. Each table ID is also duplicated, so you can go in and delete where id = ?. Anybody know a way of updating/deleting from a table of dupes like this????? Thank you!!!
June 21, 2005 at 3:00 pm
how can an id be duplicated...
what's the primary key of the table??
Can you post the table definition so we can show you the exact script?
June 21, 2005 at 3:15 pm
There is no primary key on that utility table. Why, I don't know. I could make it the ID column the primary key after I get this mess fixed. This is a table that isn't accessed alot, obviously.....and how this happened, is unclear to me.
table definition:
CREATE TABLE [dbo].[PRODUCTION_TABLES] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsAuditTable] [bit] NULL ,
[IsInFullDictionary] [bit] NULL ,
[IsInAsthmaDictionary] [bit] NULL ,
[IsInDiabetesDictionary] [bit] NULL ,
[IsInUCSFDictionary] [bit] NULL ,
[IsInNJDictionary] [bit] NULL ,
[IsDimensionTable] [bit] NULL ,
[IsIncentiveTable] [bit] NULL ,
[IsDomainTable] [bit] NULL ,
[ScripterOrder] [tinyint] NULL ,
[Created] [datetime] NULL ,
[CreatedBy] [int] NULL ,
[IsIn166] [bit] NULL ,
[IsIn176] [bit] NULL ,
[IsIn181] [int] NULL ,
[IsIn187] [int] NULL
) ON [PRIMARY]
GO
June 21, 2005 at 3:41 pm
what about a select distinct * into a temp table, dropping the dupe table and then renaming the temp to original name ?!
**ASCII stupid question, get a stupid ANSI !!!**
June 21, 2005 at 3:51 pm
June 21, 2005 at 6:41 pm
Yup... it seems to be a case where not much will be loss... Make sure you rethink the indexing strategy and recreate any default or check constraint as they will have been lost from that operation.
June 22, 2005 at 9:22 am
June 22, 2005 at 9:24 am
HTH.
June 27, 2005 at 4:11 am
try this ..........this example should work. This way you do not have to wory abt indexes etc.
Just add a column Primary_key (Identity yes & set as primary key )to perform this operation & then drop this column when duplicate are deleted.
DELETE from Item_Master
WHERE
EXISTS
(
SELECT
ITEM_CODE
FROM
Item_Master Item_Master_Inner
WHERE
Item_Master.Dup_column = Item_Master_Inner.Dup_column
AND Item_Master.Primary_key > Item_Master_Inner.Primary_key
)
GO
ENSURE THAT YOU HAVE A BACKUP OF THE DATABASE BEFORE U DO THIS
June 27, 2005 at 9:38 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply