INSERT INTO another table FROM another select doesn't work

Question

Cannot work with this SQL:

INSERT INTO final_tab (
    id_member_card
    ,points
    )
VALUES 
    (
        SELECT idMemberCard
        ,punti FROM (
        SELECT *
        FROM (
            SELECT idMemberCard
                ,SUM(amountOriginal) AS speso
                ,SUM(amountFinal) AS punti
            FROM transactions
            WHERE date_transaction_UTC BETWEEN '2018-01-01 00:00:00'
                    AND '2018-03-19 23:59:59'
            GROUP BY idMemberCard
            ) AS derived
        WHERE speso >= 50
        ) AS d2
    )

The error is

Syntax error near 'SELECT idMemberCard , punti FROM (SELECT * FROM ( SELECT idMemberCard , SUM(amou' at line 6

Where am I wrong? Thank you

Remove Values ( ) keep the SELECT inside that though. Values() is for specifying a list, but you don't have a list, you have a result set, so just INSERT INTO <table> SELECT.... — JNevill
– JNevill, Commented May 9, 2018 at 13:58

Gordon Linoff · Accepted Answer · 2018-05-09 13:59:32Z

No VALUES is needed for INSERT . . . SELECT:

INSERT INTO final_tab (id_member_card , points) 
    SELECT idMemberCard , punti
    FROM (SELECT *
          FROM (SELECT idMemberCard , SUM(amountOriginal) AS speso, 
                       SUM(amountFinal) AS punti
                FROM transactions
                WHERE date_transaction_UTC BETWEEN '2018-01-01 00:00:00' AND '2018-03-19 23:59:59'
                GROUP BY idMemberCard
              ) AS derived
          WHERE speso >= 50
         ) t2;

You can greatly simplify the logic to:

INSERT INTO final_tab (id_member_card , points) 
    SELECT idMemberCard , SUM(amountOriginal) AS speso, SUM(amountFinal) AS punti
    FROM transactions
    WHERE date_transaction_UTC >= '2018-01-01' AND 
          date_transaction_UTC < '2018-03-20'
    GROUP BY idMemberCard
    HAVING speso >= 50;

You could also specify this as a view if you didn't want to actually store the values in the table.

Yogesh Sharma · Accepted Answer · 2018-05-09 13:59:33Z

You can directly express this as :

INSERT INTO final_tab (id_member_card , points) 
SELECT idMemberCard , punti 
FROM (SELECT * FROM 
             ( SELECT idMemberCard , SUM(amountOriginal) AS speso,     
                      SUM(amountFinal) AS punti 
               FROM transactions
               WHERE date_transaction_UTC BETWEEN '2018-01-01 00:00:00' AND '2018-03-19 23:59:59'
               GROUP BY idMemberCard ) AS derived
WHERE speso >= 50

However, your core logic would be rewrite as :

INSERT INTO final_tab (id_member_card , points) 
SELECT idMemberCard ,  SUM(amountFinal) AS punti,
       SUM(amountOriginal) AS speso,     
FROM transactions
WHERE date_transaction_UTC BETWEEN '2018-01-01 00:00:00' AND '2018-03-19 23:59:59'
GROUP BY idMemberCard
HAVING speso > 50;

Collectives™ on Stack Overflow

INSERT INTO another table FROM another select doesn't work

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related