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:AccountID Items Money
nahan <Binary> 67824909
nahan <Binary> 67824909
nahan <Binary> 67824909
nahan <Binary> 67824909
nahan <Binary> 67824909this 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 triedSELECT AccountID, COUNT(AccountID) AS namecount FROM warehouse GROUP BY AccountID HAVING COUNT(AccountID) > 1which returns
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)

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 dont have documentation

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

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? :confused:

argh i feel so useless :o

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)

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



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

thanks that really helped man :thumbsup: