On 21 Feb 2003, at 14:23, Chris Corwin wrote: > What I want to know is how do I find these "double year" records?
You can find the problem userID-dueYear combinations like this: SELECT userID, dueYear, COUNT(*) FROM dues GROUP BY userID, dueYear HAVING COUNT(*) > 1; You can eliminate them and avoid them in the future by adding a UNIQUE index on userID, dueYear. You'll probably want to use ALTER IGNORE TABLE (see http://www.mysql.com/doc/en/ALTER_TABLE.html): IGNORE is a MySQL extension to ANSI SQL92. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table. If IGNORE isn't specified, the copy is aborted and rolled back. If IGNORE is specified, then for rows with duplicates on a unique key, only the first row is used; the others are deleted. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
