MySQL : INSERT INTO SELECT Statement

In MySQL, the INSERT INTO SELECT statement allows you to insert data into a table from the result set of a SELECT query. This is particularly useful when you want to copy data from one table to another or insert data based on the result of a query.

Here's the basic syntax:


INSERT INTO table1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table2
WHERE condition;
 

  • table1 is the table where you want to insert the data.
  • column1, column2, ... are the columns in table1 where you want to insert the data.
  • table2 is the table from which you're selecting the data.
  • The SELECT statement specifies the columns and possibly filters the rows you want to insert into table1.

For example, suppose you have a table named employees with columns employee_id, first_name, last_name, and salary, and you want to insert data into another table named employees_archive where the salary is above $50,000:


INSERT INTO employees_archive (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;
 

This query inserts rows into the employees_archive table, copying the employee_id, first_name, last_name, and salary columns from the employees table, but only for employees with a salary above $50,000.

It's important to ensure that the columns selected in the SELECT statement match the columns being inserted into in the INSERT INTO statement, both in number and in data type.

AI car modification