Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bootn
Starting Member
5 Posts |
 Posted - 2005-05-23 : 22:01:33
|
| I have a table called Reference, there are a little over 1300 rows in the table but there are actually only ~330 real records. Each recrod has been duplicated four times in the table, even though the primary key has a unique constraint.I wish to delete 3 (out of the four) rows for each record. I can isolate the 330 unique rows using the DISTINCT constraint. It wors fine in a SELECT query but does not work for a DELETE Query. What would be my best option? (Note Access will not display all the columns in this query as there are too many, so i can not just copy the results of the SELECT query to another table and delete the old one)Cheers for any ideas |
|
|
bootn
Starting Member
5 Posts |
Posted - 2005-05-23 : 23:54:45
|
| Ok here is what i have come up with so far. The easiest way i can see to do this is copy the unique records into another table, drop the old table and rename the new table to the old name. This wouldnt be so difficult except for the number of columns/rows that i wish to insert it comes up with an error:Here is my insert statement:///////////////////////////////////////////INSERT INTO test4SELECT DISTINCT Patent, Suffix, Claims, ApplicationNumber, DateFiled, DateExpiry, DateGranted, InformalTitle, P1, D1, P2, D2, P3, D3, P4, D4, P5, D5, P6, D6, P7, D7, P8, D8, Assignee, Image1, Image2, SummaryInvent, TeachPatent, InventiveStep, Reason, Area, Remarks, LastUpdated, Patent1, Patent2, Patent3, RefNotes, KeyWords, SoftLocation, SpermPatent, QuestionIDFROM dbo_tblReference;///////////////////////////////////////////Error Message!: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data./////////////////////////////////////////// |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-05-24 : 02:16:42
|
| To delete duplicates and keep one, try thisDelete from yourtable where primayKeycolumn not in (select min(primayKeycolumn) from yourTable group by othercolum)MadhivananFailing to plan is Planning to fail |
|
|
|
bootn
Starting Member
5 Posts |
Posted - 2005-05-24 : 03:08:34
|
| madhivanan, thanx for the reply, however, it would appear that this would not work as there is not an autonumber in the table, the primary key is a number entered in by the user.The problem is is that there is no unique constraint in the table with the exception of the Patent Number, which has been repeated four times in the table, and i want to delete 3 of the 4.Any other ideas or suggestions?Thanks again |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-05-24 : 03:27:28
|
| Can you post the sample data with expected outcome?MadhivananFailing to plan is Planning to fail |
|
|
|
bootn
Starting Member
5 Posts |
Posted - 2005-05-24 : 03:46:44
|
| I have tried multiple scripts which are just different ways of doing the same thing and I still get the same outcome, I have 3 fields that seem to be causing the problem. They are all memo fields and contain some text.I always seem to be getting the same error of:The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.If I leave these fields out i get the result I want, but these fields are important.....Have you or anyone else encounted this error and gotten around it? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-05-24 : 04:48:00
|
quote:
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
The length of destination column is smaller than that of source columnpost the table structure of table4 and dbo_tblReference;and also sample data with expected outcomeMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Duplicate Record Deletion