MYSQL, Copy selected fields from one table to another

Question

In MySQL, How do I copy a FIELD with all RECORDS from TABLE1 to TABLE2 which corresponds to a primary key ie: EMPLOYEE no.?

Answer 1

If you mean you want to update one table's column using another table's column, then here are some options:

1. A join:

   UPDATE table1 AS t1
     INNER JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   Alternatively it could be a left join:

   UPDATE table1 AS t1
     LEFT JOIN table2 AS t2 ON t1.EmpoyeeNo = t2.EmployeeNo
   SET t1.SomeColumn = t2.SomeColumn
   

   which would essentially empty (set to NULL) the values where no match occurred.

2. A subquery:

   UPDATE table1
   SET SomeColumn = (
     SELECT SomeColumn
     FROM table2
     WHERE EmployeeNo = table1.EmployeeNo
   )
   

   This is equivalent to the left join solution in #1.

Note that in all cases it is assumed that a row in table1 can match no more than one row in table2.