Danish Bin Sofwan Danish Bin Sofwan - 2 months ago 13
MySQL Question

MySQL : Insert into... Select from .... On Duplicate Key

I have a record from table A say :

_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 100

1-01-2016 | US | MTV | 50
_____________________________________________________________________________


I insert this record into another table B Using the query :

INSERT INTO B
SELECT
DATE,
country,
channel,
SUM(clicks) AS clicks
FROM
A
GROUP BY DATE,
country,
channel;


Now Table B looks like :

_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 150
_____________________________________________________________________________


At some other instant in time I get another record in table A say

_____________________________________________________________________________
Date | country | channel | clicks
_____________________________________________________________________________
1-01-2016 | US | MTV | 300
_____________________________________________________________________________


Now I want to update the old record in table B which should look like this :

_____________________________________________________________________________
Date | country | channel | clicks
_______________________________________________________________________________
1-01-2016 | US | MTV | 450
_____________________________________________________________________________


How can I do this with MySQL

Answer

You can use the same query (used to insert) along with INNER JOIN in order to update.

UPDATE B
INNER JOIN 
(
  SELECT 
    DATE,
    country,
    channel,
    SUM(clicks) AS clicks 
  FROM  A 
  GROUP BY DATE,  country,   channel
)AS t
ON B.Date = t.Date AND B.country = t.country AND B.channel = t.channel
SET B.clicks = t.clicks;

EDIT:

In order to update/insert through the same query.

INSERT INTO B SELECT
    t.DATE,
    t.country,
    t.channel,
    t.clicks
FROM 
(
    SELECT
        DATE,
        country,
        channel,
        SUM(clicks) AS clicks
    FROM A
    GROUP BY DATE, country, channel 
) AS t
ON DUPLICATE KEY UPDATE clicks = t.clicks