Cannot delete SQL server duplicate records 
Author Message
 Cannot delete SQL server duplicate records

Hi,

I have some duplicate record in a same table (SQL server 7 / 2k).

for example:
ID    NAME
01    bill
01    bill
01    bill

If I want to delete (or change) any of the record, I will get a error
message.

Anyone can help me for that?

thanks a lot,
larry



Fri, 25 Feb 2005 13:42:50 GMT  
 Cannot delete SQL server duplicate records
Larry,

Make sure you have a primary key on the table so that a row can be
identified uniquely. If there's no meaningful key that you can use, you can
create a surrogate key.

To remove duplicates you can first add a column with an identity property:

ALTER TABLE T1
  ADD key_col INT NOT NULL IDENTITY

And then issue the following query:

DELETE FROM T1
WHERE EXISTS
  (SELECT *
   FROM T1 AS T2
   WHERE T2.id = T1.id
     AND T2.key_col > T1.key_col)

At this point you can define a primary key constraint, and if the identity
column is not necessary anymore, remove it:

ALTER TABLE T1
  DROP COLUMN key_col

ALTER TABLE T1
  ADD CONSTRAINT PK_T1 PRIMARY KEY(id)

--
BG
SQL Server MVP
http://www.sql.co.il


Quote:
> Hi,

> I have some duplicate record in a same table (SQL server 7 / 2k).

> for example:
> ID    NAME
> 01    bill
> 01    bill
> 01    bill

> If I want to delete (or change) any of the record, I will get a error
> message.

> Anyone can help me for that?

> thanks a lot,
> larry



Fri, 25 Feb 2005 14:13:06 GMT  
 Cannot delete SQL server duplicate records
What is the error message? Which tool are you using? DDL statements?

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


Quote:
> Hi,

> I have some duplicate record in a same table (SQL server 7 / 2k).

> for example:
> ID    NAME
> 01    bill
> 01    bill
> 01    bill

> If I want to delete (or change) any of the record, I will get a error
> message.

> Anyone can help me for that?

> thanks a lot,
> larry



Fri, 25 Feb 2005 14:11:29 GMT  
 Cannot delete SQL server duplicate records

the error message is
"Key column information is insufficient or incorrect. Too many rows were
affected by update."



Quote:
> What is the error message? Which tool are you using? DDL statements?

> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org



> > Hi,

> > I have some duplicate record in a same table (SQL server 7 / 2k).

> > for example:
> > ID    NAME
> > 01    bill
> > 01    bill
> > 01    bill

> > If I want to delete (or change) any of the record, I will get a error
> > message.

> > Anyone can help me for that?

> > thanks a lot,
> > larry



Fri, 25 Feb 2005 15:54:33 GMT  
 Cannot delete SQL server duplicate records
thank you,
you are right. I did not set the primary key.

I input the same record twice by accident.
After that, I couldn't do anything to the two records.
I want to know if it is a easy way to fix this kind of problem.

Thanks a lot for your help,
larry


Quote:
> Larry,

> Make sure you have a primary key on the table so that a row can be
> identified uniquely. If there's no meaningful key that you can use, you
can
> create a surrogate key.

> To remove duplicates you can first add a column with an identity property:

> ALTER TABLE T1
>   ADD key_col INT NOT NULL IDENTITY

> And then issue the following query:

> DELETE FROM T1
> WHERE EXISTS
>   (SELECT *
>    FROM T1 AS T2
>    WHERE T2.id = T1.id
>      AND T2.key_col > T1.key_col)

> At this point you can define a primary key constraint, and if the identity
> column is not necessary anymore, remove it:

> ALTER TABLE T1
>   DROP COLUMN key_col

> ALTER TABLE T1
>   ADD CONSTRAINT PK_T1 PRIMARY KEY(id)

> --
> BG
> SQL Server MVP
> http://www.sql.co.il



> > Hi,

> > I have some duplicate record in a same table (SQL server 7 / 2k).

> > for example:
> > ID    NAME
> > 01    bill
> > 01    bill
> > 01    bill

> > If I want to delete (or change) any of the record, I will get a error
> > message.

> > Anyone can help me for that?

> > thanks a lot,
> > larry



Fri, 25 Feb 2005 16:11:57 GMT  
 Cannot delete SQL server duplicate records
You can add a new column in the table and fill the column with different
integers,then to delete the record.

Quote:

> the error message is
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."



> > What is the error message? Which tool are you using? DDL statements?

> > --
> > Dejan Sarka, SQL Server MVP
> > FAQ from Neil & others at: http://www.sqlserverfaq.com
> > Please reply only to the newsgroups.
> > PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org



> > > Hi,

> > > I have some duplicate record in a same table (SQL server 7 / 2k).

> > > for example:
> > > ID    NAME
> > > 01    bill
> > > 01    bill
> > > 01    bill

> > > If I want to delete (or change) any of the record, I will get a error
> > > message.

> > > Anyone can help me for that?

> > > thanks a lot,
> > > larry



Mon, 28 Feb 2005 13:03:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Deleting records in SQL server from VC++ DAO connection

2. Deleting duplicate records

3. CANNOT DELETE RECORD

4. Getting a random record from a SQL Server table

5. Locking a record in an MS-SQL Server

6. Error updating SQL-Server record via ODBC (CRecordset)

7. Problem Fetching Records in Bulk using MFC/ODBC/SQL Server

8. Adding records to SQL Server

9. CRecordset: Error when adding records SQL Server view

10. Records being locked in SQL Server database

11. ADO record binding from SQL server to String type

12. Update call to SQL Server with large record never returns

 

 
Powered by phpBB® Forum Software