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-- Pinuts media+science GmbH http://www.pinuts.de Raimund Jacob [EMAIL PROTECTED] Potsdamer Str. 96 voice : +49 30 59 00 90 322 10785 Berlin fax : +49 30 59 00 90 390 Germany
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
