CODEWITHSUNDEEP
mysql
Bryan Geltz
Bryan Geltz

Reputation: 9

SQL insert into a table after a query

So I am not sure how to approach this. I have a table that looks like this [enter image description here][1]

![1]: https://i.sstatic.net/k9Cl7.png

I need to take information from a different table and insert it into the above table.

The query to get the information that I need is as follows: 

select customerNumber, sum(quantityOrdered * priceEach) as orderTotal, curdate() as todaysDate 
from orders join orderdetails

where orders.orderNumber = orderdetails.orderNumber

group by orders.customerNumber

having orderTotal > 150000

I have tried using the:

insert into topCustomers( customerNumber, orderTotal, curdate())

before my select query and that is not how it is supposed to work.

Any guidance would be amazing !!

Thank you all in advanced

Upvotes: 1

Views: 83

Answers (1)

JNevill
JNevill

Reputation: 50200

In the parentheses of your INSERT INTO statement you put the FIELD NAMES to which you are inserting. You don't have a field name called CURDATE() and so the query is failing. Instead:

insert into topCustomers( customerNumber, orderTotal, CustomerDate) 
select customerNumber, sum(quantityOrdered * priceEach) as orderTotal, curdate() as todaysDate 
from orders join orderdetails    
where orders.orderNumber = orderdetails.orderNumber    
group by orders.customerNumber    
having orderTotal > 150000

Example:

mysql> create table test2 (cdate date);
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO test2 (curdate());
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual tcorresponds to your MySQL server version for the right syntax to use near 'cte())' at line 1

mysql> INSERT INTO test2 (cdate) SELECT curdate();
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

Upvotes: 3

Related Questions