Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I use a subquery inside an INSERT statement?

Tags:

mysql

subquery

I need to insert a row into a table, with one field value being calculated from another table. Rather than doing two queries and risking a race condition, I thought it'd be better to do it all in one statement.

INSERT INTO `myTable` (`someData`, `averageAtThisTime`)
VALUES (
    "some stuff",
    SELECT AVG(`myField`) FROM `myOtherTable`
)

... but this doesn't work. Is there a way I can achieve this in one statement? If not, what's your recommendation?

like image 357
nickf Avatar asked Mar 06 '09 02:03

nickf


2 Answers

INSERT INTO `myTable` (`someData`, `averageAtThisTime`)
select "some stuff", AVG(`myField`) 
FROM `myOtherTable`
like image 197
bstoney Avatar answered Sep 28 '22 09:09

bstoney


Your subquery shoud be enclosed in parentheses. This code should work:

INSERT INTO `myTable` (`someData`, `averageAtThisTime`)
VALUES (
"some stuff",
(SELECT AVG(`myField`) FROM `myOtherTable`)
);
like image 33
Aleksandr Kravets Avatar answered Sep 28 '22 08:09

Aleksandr Kravets