To remove duplicate records from a table. What field(s) determine a duplicate, such as
SSN, or SSN and code assignment. In my example shown I use SSN and a code that desinates a group assigned to. The SSN can have more then one unique code assignment.
First verify duplicates with below command
select ssn,p_code count(*) from p_code group by ssn,p_code having count(*) >1 The COUNT function produces a total of all records, but adding the GROUP BY there is a individual line for each unquie SSN+Code. Adding the having clause > 1 shows only the duplicates.
Next make a file of all records without the dupiclates with this command
select * , count(distinct ssn+p_code) from p_code group by ssn,p_code into table temp DISTINCT produces only one for each duplicate record and for each non-duplicate. The temp table produced will have all fields plus a field added that shows the count.
Now zap the p_code table, then append from the file temp
The extra field in the temp gets drouped when you append.