Row duplicates

  • 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!!!


    Thank you!!,

    Angelindiego

  • 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?

  • 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


    Thank you!!,

    Angelindiego

  • 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 !!!**

  • That sounds fabulous!!!  I was trying to make it harder than it needed to be!!  Thank you!! 


    Thank you!!,

    Angelindiego

  • 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.

  • Thanks Remi!!!!!!


    Thank you!!,

    Angelindiego

  • HTH.

  • 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

  • Thank you for this!!!!


    Thank you!!,

    Angelindiego

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply