Search code examples
mysqlinner-joinupdate-inner-join

MySQL UPDATE Two Tables with Differences From The First

I have two tables table1 and master. Master is the master database of contacts. Table1 contains recent contact information from a survey of respondents in a study.

table1 contains current information - both tables are connected by the common variable ID

here is the select query (it works perfectly) - it finds all the records where EITHER field1 or field2 are different between table1 and the master table

SELECT
t1.ID,
t1.field1,
m.field1,
t1.field2,
m.field2
FROM
table1 t1
INNER JOIN master m ON t1.ID = m.ID
where 
(
   (t1.field1 <> m.field1) OR
(t1.field2 <> m.field2)

)

My question is this: how can I transform the SELECT statement into an UPDATE statement so that all the records with different values in table1 overwrite the values in the master table?

Thank you.

Solution

  • You can use the following update.
    The where clause does not change the final result because we will overwrite rows which are already correct with the same value.

    UPDATE t1
INNER JOIN master ON t1.id = master.id
SET 
  t1.field1 = m.field1,
  t1.field2 = m.field2
WHERE t1.field1 <> m.field1
OR t1.field2 <> m.field2;