UPDATE table IF data in another

Question

I would like to retrieve some data from another table and put it in my first table.

I used this query:

UPDATE table_1 SET table_1.firstname = 
(
 SELECT 
 firstname 
 FROM table_2 
 WHERE table_2.lastname = table_1.lastname LIMIT 1
);

But this request timed out because I have almost 300k lines in table_1

I tried to update only one field in one record as follows:

UPDATE table_1 SET table_1.firstname = 
(
 SELECT 
 firstname 
 FROM table_2 
 WHERE table_2.lastname = "toto" LIMIT 1
);

But I got: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I was wondering if my request could be more efficient and faster?

Edit_1: I can't exactly put the structure of the tables but I represented it with the examples I'm using in my query:

*************************** 1. row ***************************
 Table: table_1
Create Table: CREATE TABLE `table_1` (
 `lastname` varchar(9) NOT NULL,
 `firstname` varchar(14) DEFAULT NULL,
 PRIMARY KEY (`lastname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
*************************** 1. row ***************************
 Table: table_2
Create Table: CREATE TABLE `table_2` (
 `lastname` varchar(9) CHARACTER SET latin1 NOT NULL,
 `firstname` varchar(14) CHARACTER SET latin1 NOT NULL,
 PRIMARY KEY (`firstname`),
 KEY `lastname` (`lastname`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

I think there is another problem because even when I don't loop over the 300k lines I'm getting a timeout.

Here the numbers of records in table_1 : 260905 and in table_2: 32,165124

• Vérace: Hi, and welcome to dba.se! Please give us the output of SHOW CREATE TABLE table_x\G for your two tables! Also the no. of records in each table. 380k isn't a very large table - there may be something else going on!
• Akina: Do not use correlated subqueries. Study multiple-table UPDATE syntax.

Answer 1

Because you're using a correlated subquery, as Akina mentioned, it probably causes a scan operation to occur against your entire table (which is 32,165,124 rows if I read that correctly). You're better off writing the query in a more relational manner which can be achieved with an INNER JOIN, for example such as this:

UPDATE table_1
INNER JOIN table_2
 ON table_1.lastname = table_2.lastname
SET table_1.firstname = table_2.firstname 

The INNER JOIN will only produce a result set where the data exists in both tables for the fields being joined by in the ON clause. Therefore only the rows that exist in table_2 and match table_1 by lastname will be used for the UPDATE and may result in a more efficient seek operation.

• Kravennagen: Thank you all, this one works perfectly ! I'm gonna study more the multiple-table update !