MySQL: Trying to populate data into one column from another table

Question

Table A has several columns, including FirstName and LastName Table B has different columns, including FirstName, LastName and EmployeeID

I added EmployeeID into Table A. I now want to populate Table A's Employee ID's from Table B's Employee ID's, using the first and last name (we currently have no one working with the same name - the design of this table was weird like that)

I've attempted a few things, but I keep coming back to

INSERT INTO TableA (EMPLOYEE_ID) A
SELECT B.EMPLOYEE_ID FROM TableB B
WHERE A.First_name = B.First_name
AND A.Last_name = B.Last_name

But I keep getting a syntax error - MySQL server version for the right syntax to use near A. I don't know how to use this syntax when dealing with Insert statements, I think, or if this is the right way at all.

Answer 1

It sounds like you already have data in TableA that you want to update. If that is the case, you need to use an UPDATE statement. Here's an example:

UPDATE TableA A
SET EMPLOYEE_ID =
  (SELECT EMPLOYEE_ID
   FROM TableB B
   WHERE B.First_name = A.First_name
   AND B.Last_name = A.Last_name)
WHERE EXISTS
  (SELECT EMPLOYEE_ID
   FROM TableB B
   WHERE B.First_name = A.First_name
   AND B.Last_name = A.Last_name)

Answer 2

Question: Are first and last name guaranteed to be unique?

Also, this needs to be an update.

UPDATE TableA A
SET a.employee_id = (SELECT employee_id FROM TableB WHERE first_name = a.first_name AND last_name = a.last_name)
WHERE EXISTS (SELECT 1 FROM TableB WHERE first_name = a.first_name AND last_name = a.last_name)