How to use SELECT inside INSERT to define a specific column in mysql

Question

I have a insert statement that need to get a specific value from another table.

So, I searched and found this question: How to insert values into an MYSQL-Table by using Select-Statements

However, I'm having difficult in my case, since I just need 1 column.

What I have right now:

INSERT INTO TABLE_A (ID_STATUS, ID_USER, ID_Q_INI, ID_Q_FIN, PRICE ) 
VALUES 
(1, 2, 10, 17, (SELECT SUM(PRICE) FROM TABLE_B WHERE (ID_Q >= 10 && ID_Q < 17))

What I want to do is: when I register a new purchase, my script only will input the status, user id, and both initial product id, and final product id.

The final price will be the sum from the products from X to Y-1.

Is what I'm trying to do even possible? Or I'll have to make a first query, to get the value and then make the insert?

possible duplicate of [How can I insert values into a table, using a subquery with more than one result?](http://stackoverflow.com/questions/9692319/how-can-i-insert-values-into-a-table-using-a-subquery-with-more-than-one-result) — marekful, Jun 30 '15 at 17:28

score 2 · Accepted Answer · answered Jun 30 '15 at 17:36

2

I believe you're missing a ) at the end of your query

your query after some formatting:

INSERT INTO TABLE_A
    (ID_STATUS, ID_USER, ID_Q_INI, ID_Q_FIN, PRICE ) 
VALUES 
    (
        1,2,10, 17, (
            SELECT SUM(PRICE) 
            FROM TABLE_B
            WHERE (ID_Q >= 10 && ID_Q < 17)
        )

answered Jun 30 '15 at 17:36

Rednax

88
5

Omg, This is what I was doing wrong all the time. LOL Thank you. – Kio Coan Jun 30 '15 at 17:42

score 0 · Answer 2 · answered Jun 30 '15 at 17:26

I think this will work (not tested)

INSERT INTO TABLE_A (ID_STATUS, ID_USER, ID_Q_INI, ID_Q_FIN, PRICE ) 
SELECT 1, 2, 10, 17
, SUM(PRICE) 
FROM TABLE_B 
WHERE ID_Q >= 10 
AND ID_Q < 17

Maybe you will need to add a grouping on 1, 2, 10 and 17

INSERT INTO TABLE_A (ID_STATUS, ID_USER, ID_Q_INI, ID_Q_FIN, PRICE ) 
SELECT 1, 2, 10, 17
, SUM(PRICE) 
FROM TABLE_B 
WHERE ID_Q >= 10 
AND ID_Q < 17
GROUP BY 1, 2, 10, 17

How to use SELECT inside INSERT to define a specific column in mysql

2 Answers2