MySQL: Trying to populate data into one column from another table
Solution 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)
Solution 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)
RankWeis
Updated on July 20, 2022Comments
-
RankWeis almost 2 years
Table A has several columns, including
FirstName
andLastName
Table B has different columns, includingFirstName
,LastName
andEmployeeID
I added
EmployeeID
into Table A. I now want to populate Table A'sEmployee ID
's from Table B'sEmployee 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. -
RankWeis almost 13 yearsWorked perfectly, thanks. I'm very rusty on my SQL skills, so I forgot about Update completely
-
RankWeis almost 13 yearsThey are. At this point the company only has 20 people, so I needed to get on this before we start growing more. I don't know why anybody thought it was a good idea to not include a key to employee names
-
subbu about 10 yearsI get an error like this: Subquery returns more than 1 row. Any suggestions on it.
-
IndexOutOfDevelopersException almost 7 yearsWhy do we have to parts in update? The first part is straith forward (Select ..) and the second part is WHERE EXISTS (Select)? Is it only possible to use WHERE EXISTS? That means: UPDATE TAbleA A SET a.employee_id WHERE EXISTS (SELECT ...).
-
Saurabh Tiwari over 5 yearsIts awesome how exists is used here to update multiple records.