Mysql 'distinct' (unique)

[meghatronic]

I have a table with three fields: "id", "desc" and "db".

I want "id" and "db" to be the composite primary key, however the table as it stands at the moment is not unique.

I want to create a new table with only unique values.

The difficulties I'm having is id on its own doesn't have to be unique, neither does db on its own... it is the id/db combination that is unique.

Any pointers?

 

[matja]

how about fixing the values in your current table so that they are unique composite primary keys, and running 'alter table t add primary key (id, db);'?

If you have to do it with minimal downtime, you could create a new table with an identical definition, insert...select the rows from old to new, fix the values, alter the primary key, drop the original, and "alter table...rename" the new back to the old.

Or am i getting the wrong idea...

 

[meghatronic]

Or am i getting the wrong idea...

Nope, you've not got the wrong idea. But,

how about fixing the values in your current table so that they are unique composite primary keys

Is what I'm stuck on. Ofcourse I could do it in excel, but I wanna do it directly in SQL.

In pseudocode, what I want is "select all rows where id+db (combined) = unique". As id isn't unique, and db definately is, but the combination should be.

 

[matja]

you could use concat() and seperate the column values with a value which doesn't appear in the data - eg : select distinct concat(id,'|',db) from t;

 

[meghatronic]

you could use concat() and seperate the column values with a value which doesn't appear in the data - eg : select distinct concat(id,'|',db) from t;

I can use the above to build a new table and I can include the missing column (desc)

Cheers


EDIT -Can anyone make the below syntaxically correct?
db_ref has "id, db and def", db_reg_bck has "id, db" but no def.

SELECT db_refs.`id`, db_refs_bck.`desc`, db_refs.`db` FROM db_refs LEFT JOIN db_refs_bck ON concat(db_refs.id,'|',db_refs.db) = concat(db_refs_bck.id,'|',db_refs_bck.db)