MySQL : Insert with Nested Select Queries - DevDummy

Latest

Views | Thoughts | Concepts | Techniques

Thursday, May 04, 2023

MySQL : Insert with Nested Select Queries


It is always convenient to copy a selected set of data to a table directly from another table using nested select queries.

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

In this example, the INSERT statement inserts data into table1 and specifies the columns to insert data into (column1, column2, and column3). The SELECT statement nested within the INSERT statement selects the data to be inserted from table2. The WHERE clause can be used to filter the data to be inserted based on specific conditions.

Here's another example that demonstrates a more complex nested SELECT query:

INSERT INTO table1 (column1, column2, column3) SELECT column1, column2, column3 FROM ( SELECT column1, column2, column3 FROM table2 WHERE condition1 UNION SELECT column1, column2, column3 FROM table3 WHERE condition2 ) AS temp_table;

In this example, the nested SELECT query is itself a union of two SELECT statements that select data from table2 and table3 based on certain conditions. The resulting data set is then inserted into table1.

Note that when using nested SELECT queries in an INSERT statement, it's important to ensure that the columns being inserted into match the columns being selected in the nested SELECT query. Otherwise, you may encounter errors. 

References


No comments:

Post a Comment