CODEWITHSUNDEEP
mysqlselectinserton-duplicate-key
Tjeerd Kramer
Tjeerd Kramer

Reputation: 223

INSERT from SELECT with ON DUPLICATE KEY UPDATE

I am trying to run an INSERT query, based upon a SELECT, with an "ON DUPLICATE KEY UPDATE" statement. The SELECT query works, and the resulting data if I were to input it "manually" will result in a duplicate key issue. So far so good. However, the query below does not seem to update the value in "et_report_ymd.quotes" as I expected it to do.

INSERT IGNORE INTO et_report_ymd
SELECT 
    NULL,
    t.year AS year,
    t.month AS month,
    t.day AS day,
    SUM(t.quotes) AS quotes

FROM source_table AS t

GROUP BY t.year, t.month, t.day

ON DUPLICATE KEY UPDATE 
    et_report_ymd.quotes = quotes

All help is welcome...

Upvotes: 2

Views: 587

Answers (1)

Tjeerd Kramer
Tjeerd Kramer

Reputation: 223

And just when you decided to ask for help, you come up with the solution, as always. 

INSERT IGNORE INTO et_report_ymd
SELECT 
    NULL,
    t.year AS year,
    t.month AS month,
    t.day AS day,
    SUM(t.quotes) AS quotes

FROM source_table AS t

GROUP BY t.year, t.month, t.day

ON DUPLICATE KEY UPDATE 
et_report_ymd.quotes = VALUES(quotes)

Note the "VALUES(quotes)" part at the end of the query instead of just "quotes".

Upvotes: 3

Related Questions