| Visual FoxPro Tips |
| |
| Finding Duplicate Records |
| |
| Sometimes it is important to
identify duplicated records in a table. For example,
you might have a mailing list and you want to make
sure you don't have the same customer in it more
than once. So how do you find the duplicates? One
way is to write a program that steps through the
table one record at a time and for each record scan
the rest of the table. However, there is an easier
and more efficient approach using SQL: |
| |
| SELECT first_name + last_name
; |
| |
FROM cust_table ;
GROUP BY first_name + last_name ;
HAVING COUNT(first_name + last_name) > 1 |
| |
| What if you want to see each
of the records that has a duplicate instead of just
a list of names that are duplicated? Here is an
example: |
| |
| SELECT *; |
| |
FROM cust_table ;
WHERE first_name + last_name in ;
(SELECT first_name + last_name ;
FROM cust_table ;
GROUP by first_name + last_name ;
HAVING COUNT(first_name + last_name) > 1);
INTO CURSOR duplicates |
| |
| This tip is provided to you
by Foxy Classes.
For more Visual FoxPro tips,
click here. |
