When adding records in SQL language, you sometimes need to get records from different tables. In such cases, INSERT and SELECT statements must be used together.
In this article you will see the use of INSERT and SELECT statements together.
You’ll also find examples of the article’s title “Insert Into Inner Join”.
I used the following database for the following queries.
SQL Example: Write the query that adds student number 5 as a writer to the author table
|
1 2 3 4 5 6 |
INSERT INTO authors(name,surname) SELECT name,surname FROM students WHERE studentId=5 |
SQL Example: Write the query that adds the author table as the author of the 10 oldest students in the student table. (INSERT INTO, SELECT)
|
1 2 3 4 5 6 7 |
INSERT INTO authors(name,surname) SELECT name,surname FROM students ORDER BY birthdate LIMIT 0,10 |
SQL Example: This query is not very meaningful, but it was created to use INSERT, SELECT, and JOIN concepts together. In the example, book name, author name and author surname are added to the author table as the book name.
|
1 2 3 4 5 6 |
INSERT INTO books(name) SELECT CONCAT(books.name,' ',authors.name,' ',authors.surname) FROM books INNER JOIN authors ON authors.authorId=books.bookId WHERE books.bookId=5 |
I hope the examples have been positive for your application.
Can you give more specific example. I cannot understand. Like where is “primary key’ and where is “foreign key”. Because in my code, I don’t have “where” code.
This is my code:
LIST DATA