CODEWITHSUNDEEP
mysqlsql
sineverba
sineverba

Reputation: 5172

INSERT INTO another table FROM another select doesn't work

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

Upvotes: 0

Views: 45

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 4

Related Questions