Delete Rows

[rwies]

I am working on a conversion and I have run into a problem concerning duplicate rows. I ultimately have to insert the data in the table where the conversion is being done into another table. In the new table three fields make up the primary key. Due to various factors the in the conversion process I end up with multiple rows where the three (primary key) fields are duplicates.

I am using the following code to find the duplicates:

SELECT pkey, COUNT(pkey) AS Expr1
FROM AccountZoneTbl0
GROUP BY pkey
HAVING (COUNT(pkey) > 1)
ORDER BY pkey

pkey is a field that contains a string that consists of the three fields that make up the primary key in the table where the data will be inserted.

From this code I find 160 duplicates most with just 2 copies of the pkey field but some with as many as 10.

I need to delete all but one of these rows. It is important that one remain but the others must go.

Does anybody have any idea as to how I can write a routine that will delete all but one row where the pkey field is duplicated?

Thaks in advance for any assistance you are able to provide.

Ron--

 

[Gooser]

What about using DISTINCT?



v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

The Universal Model

 

[rwies]

This greatly deepens the amount of information you have to understand.

The initial code that creates the problem is:

insert into dbo.AccountZoneTbl0
(AccountNumber, S_ActNbr, System_no, MAS_ZoneID, o_mas_zoneid, fmt,
[Description], GroupID, zone_to_restore, DateTimeStamp, RestoreDelay,
zonetbl, mas_event, I_1_Evt,
eventtype, Ins)

select distinct
AccountNumber, S_ActNbr, System_no, cid_zn, o_mas_zoneid, 'cid',
[Description], GroupID, o_mas_zoneid, DateTimeStamp, RestoreDelay,
1, mas_event, mas_event,
eventtype, 1

from dbo.AccountZoneTbl0
where fmt = 'cid' and o_mas_zoneid = MAS_RestoreCode and zoneid not like '%000'

In this case, the items selected are overall distinct but the fields that make up pkey are System_no, Mas_Zoneid and another field that will be added in another function called zonestate_id. The field pkey is assembled in a latter step by combining the three fields.

It would be easiest if I can just find the duplicates and delete all but one of the rows where pkey is duplicated.

Ron—

 

[SQLSister]

the only way to do that is to find out which of these records is the correct one if there are multiples and they are not alike. If the information is differnt how would you know which one is correct?

Questions about posting. See faq183-874

 

[rwies]

For this project, all I need to do is keep one row and delste the rest. It doesn't matter which ones get deleted or which one gets kept.

There is a primary key automatically assigned with the insert. If I can figure out a way I will keep the lowest number in the series and delete the rest.

Ron--

 

[SQLSister]

select min(idfield) , field2, field3 from table1 group by field1, field2

that assumes you use the fields which would make it a unique record. Now you know which ids you want to keep and you can left join to this list where id is null and find the records you want to delete. Do not do this without making a backup of the table first in case something goes wrong and try it on a development db first.

Questions about posting. See faq183-874