MYSQL: Is there a way to Insert Values from another Table with ON DUPLICATE KEY UPDATE?

Question

my Question: Is there a way to Insert Values from another Table with ON DUPLICATE KEY UPDATE? But i use also SUM in this statement

here is my example:

INSERT INTO currencyStatistics (note, currency, amount) 
SELECT note, currency, SUM(amount) as amount2 
FROM currency 
WHERE date<1612188495 
GROUP BY note 
ON DUPLICATE KEY UPDATE amount=amount+amount2;

MySQL returns Error: "#1054 - Unknown column 'amount2' in 'field list'"

Bonus Information: the Table currencyStatistics has 4 columns: note, currency, amount and lastUpdate. I didnt added last Update on Insert, cause its a timestamp with default "current_timestamp(6)" and extra "ON UPDATE CURRENT_TIMESTAMP(6)"

Could please someone help me fix this issue?

Greetings Me

EDIT: added right error message

Answer 1

INSERT INTO currencyStatistics (note, currency, amount) 
SELECT note, currency, SUM(amount)
FROM currency 
WHERE date<1612188495 
GROUP BY note 
ON DUPLICATE KEY 
UPDATE amount = amount + VALUES(amount);

In UPDATE:

• amount is the column of the updated table (currencyStatistics)
• VALUES(amount) is the value that would have been inserted into the amount if there had been no duplication (i.e., the SUM(amount) value)