Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts

    ms sql 2000 - delete non-unique records

    i am running a backend database for an application on a ms sql 2000 (dev) server, i cant change the schema for the db as it wudmess up the application but for some reason in a few cases one table seems to be coming up with the same record 2 - 5 times when it should only be there once, eg:
    Code:
    AccountID	Items	Money
    nahan	<Binary>	67824909
    nahan	<Binary>	67824909
    nahan	<Binary>	67824909
    nahan	<Binary>	67824909
    nahan	<Binary>	67824909
    this is particularly bad as each of those binary fields is 1200 bytes long, my dadabase is already almost 9gb in size (im guessing whoever made it originally was just dumb, it has 4000 accounts in it and 90 tables mind you)

    how can i use enterprise manager or sql to delete the excess records and preferably set the field properties up so that AccountID cant be duplicated?

    from another thread in this forum i tried
    Code:
    SELECT AccountID, COUNT(AccountID) AS namecount FROM warehouse GROUP BY AccountID HAVING COUNT(AccountID) > 1
    which returns
    Code:
    AccountID	namecount
    daneport	3
    marino	2
    nahan	5
    ...	...
    but i dont know how to use this to delete the excess records only (if i tried i would probably delete too much or mess up the table or something)
    Last edited by ghell; 06-07-2005 at 09:32 AM.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    for the deletes, i think you have to do it manually. First count how many duplicates there are, then fire delete statements to the database for every duplicate recordcount minus one

    to prevent duplicates, put a unique constraint on the AccountID field. See your documentation on how to do it. But the application can give an error on an insert, if the value to be entered violates this unique constraint, so you might have to change the app a bit
    I am the luckiest man in the world

  • #3
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    i dont have documentation

    i ddint make the app and i dont have the source, im editing db to customise it a bit sorta

    app seems to be able to handle a lot of errors and its prob ok with that update error, i duno.. if it isnt can i take it off again?

    how can i put unique on? i cant find it in enterprise manager or the help that comes with it, can i do in sql like with identities?, if so how?

    can i do anything like
    delete top (count(accountid)-1)

    or anything?

    argh i feel so useless

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Quote Originally Posted by ghell
    i cant find it in enterprise manager or the help that comes with it, can i do in sql like with identities?, if so how?
    from the sql books online that com with the SQL server installation:

    To create a unique constraint
    • In your database diagram, right-click the table that will contain the constraint, then select Properties from the shortcut menu.
      -or-
    • Open the Table Designer for the table that will contain the constraint, right-click in the Table Designer, and choose Properties from the shortcut menu.
    • Choose the Indexes/Keys tab.
    • Choose New. A system-assigned name appears in the Index name box.
    • Under Column name, expand the list of columns and select the column that you want to attach the constraint to. To attach the constraint to multiple columns, select the additional columns in subsequent rows.
    • Select the Create UNIQUE check box.
    • Select the Constraint option.

    if it doesn't work, you can take it off again.

    For the deletes, i have to experiment a little. Will return with results (or without results)
    I am the luckiest man in the world

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts

    for the deletes of multiple entries

    Please please test it on a database backup before you execute this in production. I tested it a database with a warehouse-table with only the account field, there it works. It deletes all minus one records from this table where there are duplicate entries in the accountID field. So the remaining table has one entry for each accountID left.

    But test it before use, no refund if it doesnt work or destroys your productiondatabase


    Code:
    -- Declare the variables to store the values returned by FETCH.
    DECLARE @accountId varchar(40), @appearances int
    
    -- Get the recordset indicating the AccountId with duplicate entries
    DECLARE duplicate_cursor CURSOR FOR
    SELECT AccountID, COUNT(AccountID) AS namecount
    FROM warehouse
    GROUP BY AccountID
    HAVING COUNT(AccountID) > 1
    
    -- Open the recordset
    OPEN duplicate_cursor
    
    -- Perform the first fetch and store the values in variables.
    FETCH NEXT FROM duplicate_cursor
    INTO @accountId, @appearances
    
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- delete all records for this accountId minus 1
       -- Determine how many records must be deleted
       SET @appearances = @appearances - 1
    
       -- Limit the result of this delete to the above calculated maximum
       SET ROWCOUNT @appearances
    
       -- Execute the delete
       DELETE warehouse
       WHERE AccountID = @accountId
    
       FETCH NEXT FROM duplicate_cursor
       INTO @accountId, @appearances
    END
    
    CLOSE duplicate_cursor
    DEALLOCATE duplicate_cursor
    
    -- Reset the rowcount limits
    SET ROWCOUNT 0
    GO
    I am the luckiest man in the world

  • #6
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    thanks that really helped man


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •