update based in select

Hello Guys,

I have 2 tables and I need to mount an update based on the results of a select. example:

Table1
name, phone, record, age

Table2
name, phone, record, age

Both tables have the same structure. I would like to check if customers table1 exists in table2 if there is an update to make updating the data from table1 to table2
eduardo12foxAsked:
Who is Participating?
 
johanntagleConnect With a Mentor Commented:
You want to update if there are common records, not insert new records, right?  Assuming the name column is the unique identifier:

update table2 t2 join table1 t1 on t2.name=t1.name
set t2.phone=t1.phone, t2.record=t1.record, t2.age=t1.age;

If you want to both update existing and insert those in table1 that are not in table2:

1.  Make sure name column is either primary key or has unique index.
2. Execute:

replace into table2 (name, phone, record, age)
select name, phone, record age from table1;

See http://dev.mysql.com/doc/refman/5.5/en/replace.html for more info on the REPLACE keyword.
0
 
AielloJCommented:
eduardo12fox,


INSERT INTO table2 (name, phone, record, age)
SELECT name, phone, record, age
FROM table1
WHERE (name, phone, record, age) NOT IN
       (SELECT name, phone, record, age
        FROM table2);

Best regards,

AielloJ
0
 
eduardo12foxAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.