Learn SQL Database Programming

Using INSERT, UPDATE, and DELETE with non-correlated subqueries

To show you an example of inserting with a non-correlated subquery, execute the following query to create a new table to hold the data:

USE lahmansbaseballdb; CREATE TABLE salaries_avg ( teamID varchar(3) NOT NULL, salaryavg double NOT NULL );

Once you've created the table with the preceding script, you can execute the following script to insert data into the table:

USE lahmansbaseballdb; INSERT INTO salaries_avg SELECT teamid, average_salary FROM (SELECT teamid, AVG(salary) average_salary FROM salaries GROUP BY teamid) AS team_salary WHERE team_salary.average_salary > 2000000;

Here, you can see that the non-correlated subquery is actually in the SELECT clause of the INSERT ...