MySQL is a popular open-source relational database management system used for storing and managing data. One of the key features of MySQL is its ability to insert data from one table into another, allowing for easy data manipulation and organization.
In this tutorial, we will explore how to use the INSERT INTO statement to insert data from one table into another table in MySQL.
To begin, let's first create two tables in our database: "students" and "grades". The "students" table will contain information about the students, such as their name, age, and class. The "grades" table will store their grades for different subjects.
To create the "students" table, we can use the following SQL query:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
class VARCHAR(50)
);
Next, let's insert some sample data into the "students" table:
INSERT INTO students (name, age, class)
VALUES ('John Smith', 18, 'Senior'),
('Jane Doe', 17, 'Junior'),
('Bob Johnson', 16, 'Sophomore');
Now, let's create the "grades" table using the following query:
CREATE TABLE grades (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
subject VARCHAR(50),
grade INT
);
The "grades" table has a column called "student_id" which will be used to link each grade to a specific student in the "students" table.
To insert data from the "students" table into the "grades" table, we can use the INSERT INTO statement with a SELECT subquery. The SELECT subquery will retrieve the data from the "students" table and insert it into the "grades" table.
The syntax for the INSERT INTO statement with a SELECT subquery is as follows:
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM table_name2
WHERE condition;
In our case, the query will look like this:
INSERT INTO grades (student_id, subject, grade)
SELECT id, 'Math', 90
FROM students
WHERE name = 'John Smith';
This query will insert a grade of 90 for the subject "Math" into the "grades" table for the student with the name "John Smith". We can also insert grades for multiple students at once by using the WHERE clause to filter for specific names or classes.
For example, if we want to insert grades for all students in the "Junior" class, we can use the following query:
INSERT INTO grades (student_id, subject, grade)
SELECT id, 'English', 85
FROM students
WHERE class = 'Junior';
This will insert a grade of 85 for the subject "English" for all students in the "Junior" class.
We can also use the INSERT INTO statement with a SELECT subquery to insert data from multiple tables into one table. For example, if we have a third table called "attendance" that tracks the attendance of each student, we can use the following query to insert data from both the "students" and "attendance" tables into the "grades" table:
INSERT INTO grades (student_id, subject, grade)
SELECT students.id, 'Attendance', attendance.attendance_status
FROM students
INNER JOIN attendance ON students.id = attendance.student_id;
This query uses a JOIN statement to link the "students" and "attendance" tables together and inserts the attendance status for each student into the "grades" table.
In conclusion, the INSERT INTO statement with a SELECT subquery is a powerful tool in MySQL that allows us to insert data from one table into another, making it easy to organize and manipulate data in our databases. By using this feature, we can save time and effort in managing our data and ensure the accuracy and consistency of our database records.
