CODEWITHSUNDEEP
mysqlinsert
Kio Coan
Kio Coan

Reputation: 581

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

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?

Upvotes: 2

Views: 1241

Answers (2)

Rednax
Rednax

Reputation: 88

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)
        )

Upvotes: 2

A.D.
A.D.

Reputation: 1180

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

Upvotes: 0

Related Questions