mysql - Update one table with unique values from another? -

we started 1 table project , realized needed multiple tables wanted do.

we started switch over, want switched 1 table multiple.

what have now:

table: contact +-----+-------+--------+----------+ | id  | fname | lname  | phone    | +-----+-------+--------+----------+ | 123 | john  | doe    | 555-1234 | | 124 | mary  | shelly | 555-5857 | | 125 | jane  | doe    | null     | +-----+-------+--------+----------+  table: phone +----+--------+----------+------+ | id | con_id |   phone  | main | +----+--------+----------+------+ | 1  | 125    | 555-5857 | n    | +----+--------+----------+------+ 

so have few added , changed. now, need add data isn't in phone table contact table. temp table:

table: temp +------------+----------+------+ | foreign_id |   phone  | main | +------------+----------+------+ | 123        | 555-1234 | y    | | 124        | 555-4153 | y    | | 125        | 555-5857 | n    | +------------+----------+------+ 

but when add temp phone, end duplicate entries (in example, contact.id = 125).

this trying to:

table: contact +-----+-------+--------+ | id  | fname | lname  | +-----+-------+--------+ | 123 | john  | doe    | | 124 | mary  | shelly | | 125 | jane  | doe    | +-----+-------+--------+   table: phone +----+--------+----------+------+ | id | con_id |   phone  | main | +----+--------+----------+------+ | 1  | 125    | 555-5857 | n    | | 2  | 123    | 555-1234 | y    | | 3  | 124    | 555-4153 | y    | +----+--------+----------+------+ 

commands i've run:

create temporary table temp (select t2.id, phone contact t2); alter table temp add main varchar(1); update temp set main = "y";  insert phone (con_id, phone, main) select id, phone, main temp; drop table temp; 

and eventually, i'll remove column "phone" contact. problem is, if there entry in table phone number, i'm left duplicates. how prevent that?

also, if i'm doing wrong, can change too. figured temp table might best way?

you can avoid duplicates adding primary key (or unique key) phone table:

alter table phone add primary key (con_id); 

this ensure have 1 entry each con_id. if want allow multiple phone numbers each con_id, should use instead:

alter table phone add primary key (con_id, phone); 

now, can insert entries directly contact table - or temp table, if have dropped phone column in contact table:

replace phone (con_id,phone,main)     select id, phone, "y" contact; 

alternatively, can use insert ... on duplicate key update ... construction. if don't want overwrite keep original non-primary key values, use insert ignore instead.

for more details insert syntax, consult: http://docs.oracle.com/cd/e17952_01/refman-5.1-en/insert.html