CODEWITHSUNDEEP
mysqlsql
Pankaj Agrawal
Pankaj Agrawal

Reputation: 1659

Insert a value from one table to another with mysql

I have two tables one is books and another is authors.

Books table - 

id |    name    | auth_id | auth_name
1  |    Book1   |  1      | null
2  |    Book2   |  2      | null
3  |    Book3   |  3      | null

Authors table 

id | firstname | lastname 
1  |    John   |  Stark    
2  |    Jobs   |  Steve     
3  |    Amar   |  Shrama

Here auth id refers to the primary key of the Authors table. Now I want to fill the auth_name column of book table from Authors table.

I am using the following query for this. 

INSERT INTO `books` (auth_name)
SELECT CONCAT(`lastname`, ',', `firstname`)
FROM `Authors`
Left join `books` on `books`.`auth_id` = `Authors`.`id`;

But it's not working. Can anyone tell me what I am doing wrong here?

Thanks.

Upvotes: 0

Views: 169

Answers (2)

Ali Faris
Ali Faris

Reputation: 18630

use this query

UPDATE books 
SET auth_name = (SELECT CONCAT(lastname, ',', firstname) FROM authors WHERE id = auth_id)

Upvotes: 0

Mittal Patel
Mittal Patel

Reputation: 2762

You need to update the data not to insert. You can achieve it as below:

UPDATE books
INNER JOIN books ON books.auth_id = authers.id
SET books.auth_name = CONCAT(lastname, ',', firstname)

If "Authors" tables have all the author id which is in books then it is left join is not needed, if that is not the case then use the left join.

Upvotes: 2

Related Questions