CODEWITHSUNDEEP
mysql
Danish Bin Sofwan
Danish Bin Sofwan

Reputation: 476

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

Upvotes: 2

Views: 5862

Answers (1)

1000111
1000111

Reputation: 13519

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

Upvotes: 5

Related Questions