MySQL - Insert - select multiple rows with foreign key relation

0
votes

I have the below tables in MySQL.

  • Table1 (ID, FirstName, LastName, TelephoneNum)
  • Table2 (TID, TEmail)

I have 25000 rows in Table1 and my Table2 contains 20000 rows. My TID in Table2 is the foreign key referring to the primary key ID of Table1. If the LastName is NULL, then those ID's would not be present in Table2. Now, I am trying to merge the two tables only for those with the last name.

I redefined the schema of Table2 as below.

  • Table2(TID, TEmail, TFirstName, TLastName)

Now, I tried the below query to insert the firstname and lastname values into Table 2. 

insert into Table2 (TFirstName, TLastName) select FirstName,LastName from Table1 where ID = Table2.TID

However, the above query gives me an error. I can use cursor/stored procedure. But was looking to achieve it through a query itself if possible.

1
mysql

1 Answers

2
votes

I think that what you want to be doing here is an UPDATE rather than an INSERT query. INSERT is for creating additional rows, while UPDATE is for changing values.

UPDATE Table2 JOIN Table1
ON Table2.TID = Table1.ID
SET Table2.TFirstName = Table1.FirstName,
SET Table2.TLastName = Table1.LastName;