Duplicate records 1

[jvande]

I have a problem where some how I got two duplicate records in one of my SQL tables. I don't know how this happened but the problem that I face now is that I can't delete the records. Is there some kind of lock that is dissallowing me to delete these records? I know that the table isn't linked to any other table so therefore there are no dependencies. Why can't I delete either of these records?

Thanks Josh

 

[tlbroadbent]

How are you trying to delete the records? What kind of message are you getting when you attempt a delete? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[Guest_imported]

Error message "Key column information is insufficient or incorrect. Too many rows were affected by update." I am deleting it by highlighting the record and then hitting delete. I can delete other records this way, just not the duplicates.

 

[jvande]

Error message "Key column information is insufficient or incorrect. Too many rows were affected by update." I am deleting it by highlighting the record and then hitting delete. I can delete other records this way, just not the duplicates.

 

[tlbroadbent]

The problem is that the records are not unique so SQL Server cannot identify which record to delete. I assume you are using Enterprise Manager or perhaps Access to view and try to delete the records. Both programs will encounter this problem.

You can add a timestamp column to the table. Run a query like the following to update the timestamp column. This will not change any of the existing data.

Update tbl set col1=col1

After running this query, you should be able to browse and delete the duplicate records. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[jvande]

I don't understand "Update tbl set col1=col1"
sql command.
if my table is paf6 and my new column is id with data type time stamp I would put in the following code correct.
Update paf6
set id=id

 

[tlbroadbent]

You should choose any column in the table except the timestamp column as timestamp columns cannot be updated. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[jvande]

I don't understand what I am doing wrong. The timestamp column that I created isn't updating. It just say's <Binary> for all the records. After I run
update paf6
set chronid=chronid

 

[tlbroadbent]

A timestamp column is a binary column. Don't worry about the content. It is controlled by SQL. At this point you should be able to delete the duplicate records. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[jvande]

Yes, I see that. I apoligize for my ignorance. I did look through SQL books online thouroughly before visiting this forum. Believe it or not, I am actually somewhat SQL savvy, with the exception of this case.

Thank you for your help.