the HAVING clause is what you need. Let's say A is the column you want to set to unique, try a sql statement like this one
SELECT A FROM YourTable GROUP BY A HAVING COUNT(*) > 1
Good luck!
Raimund Jacob wrote:
hi all!
rather silly sql question: i want to make a column unique by adding a unique index to it. some of the data wants to prevent this by being not unique, though :)
i want to see the records that have non-unique values so that i can remove/change them manually.
the first approach is to simply add the unique index which (to no surprise) results in error 250 - duplicate secondary key. unfortunately it does not tell me anything about the offending row.
the second approach is to construct a join:
select t1.somepk, t2.somepk, t1.value shared_value from badtable t1, badtable t2 where t1.value = t2.value and t1.somepk <> t2.somepk
...which creates too a large result set (> 11 GB where only 500 MB of permanent data (by far not everything in badtable) exist). the badtable has about 140.000 rows and about a dozen columns.
okay, the third apprach is to extract only somepk and value from the table (create newtable as select somepk, value from badtable) and to run the query again. but this has the very same effect -> too much result data. somepk is fixed(20), value is a varchar(128) if that's of any interest.
okay, now i am out of ideas. this was kernel 7.4.3.17 - should i upgrade? any advice ?
thanks in advance, Raimund
-- Yves Trudeau, Ph. D. Analyste syst�me T�treau Dumont Trudeau consultants inc.
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
