Mysql update query with select with join

Question

I'm trying to run mysql update query with select in it , but I'm getting an error. Query is this:

UPDATE keywords_stats_google_temp SET (Impressions_chg, Clicks_chg, AveragePosition_chg, Ctr_chg, AverageCpc_chg, CurrentMaxCpc_chg, FreeJoins_chg, PaidJoins_chg) = (SELECT 
                SUM(Impressions) AS Impressions,
                SUM(Clicks) AS Clicks,
                SUM(Impressions*AveragePosition)/SUM(Impressions) AS AveragePosition,
                (SUM(Clicks)*revenue_price)/SUM(Impressions) AS Ctr,
                SUM(Spend)/SUM(Clicks) AS AverageCpc,
                CurrentMaxCpc,
                SUM(free_joins) AS FreeJoins,
                SUM(paid_joins) AS PaidJoins
                FROM (SELECT KeywordId FROM keywords_stats_google_temp) a JOIN keywords_stats_google_naughtymeetings b ON b.KeywordId = a.KeywordId WHERE b.TimePeriod >= '2012-04-01 00:00:00'
                AND b.TimePeriod <= '2012-04-23 00:00:00' GROUP BY a.KeywordId, MatchType)

But I'm getting only "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(Impressions_chg, Clicks_chg, AveragePosition_chg, Ctr_chg, AverageCpc_chg, Curr' at line 1"

Can anybody help me with this?

Thanks!

Actually I checked an article and there is such a way: karlssonondatabases.blogspot.com/2009/01/… — pocko
– pocko, Commented Jun 6, 2012 at 10:53
Yes, it's possible. But not with this syntax. You'll have to rewrite the statement using MySQL syntax for UPDATE. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 6, 2012 at 10:56
What is confusing is the MatchType in the GROUP BY. Is there a relevant field in the table to be updated? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 6, 2012 at 11:03

ypercubeᵀᴹ · Accepted Answer · 2012-06-06 11:16:17Z

9

You can't have SET (a,b) = (value_a, value_b) in MySQL.

Rewrite the query. Something like this:

UPDATE 
    keywords_stats_google_temp AS u
  JOIN
    ( SELECT 
          SUM(Impressions) AS Impressions,
          SUM(Clicks) AS Clicks,
          SUM(Impressions*AveragePosition)/SUM(Impressions) AS AveragePosition,
          (SUM(Clicks)*revenue_price) / SUM(Impressions) AS Ctr,
          SUM(Spend)/SUM(Clicks) AS AverageCpc,
          CurrentMaxCpc,
          SUM(free_joins) AS FreeJoins,
          SUM(paid_joins) AS PaidJoins
      FROM keywords_stats_google_naughtymeetings AS b  
      WHERE b.TimePeriod >= '2012-04-01 00:00:00'
        AND b.TimePeriod <= '2012-04-23 00:00:00' 
      GROUP BY KeywordId, 
               MatchType
    ) AS tmp
        ON  tmp.KeywordId = u.KeywordId 
        AND tmp.MatchType = u.MatchType
SET 
  u.Impressions_chg     = tmp.Impressions, 
  u.Clicks_chg          = tmp.Clicks, 
  u.AveragePosition_chg = tmp.AveragePosition, 
  u.Ctr_chg             = tmp.Ctr,
  u.AverageCpc_chg      = tmp.AverageCpc, 
  u.CurrentMaxCpc_chg   = tmp.CurrentMaxCpc, 
  u.FreeJoins_chg       = tmp.FreeJoins, 
  u.PaidJoins_chg       = tmp.PaidJoins ;

edited Jun 6, 2012 at 11:16

answered Jun 6, 2012 at 11:08

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pocko Over a year ago

I run this query and it looks like it's working, but it's very very slow. Probably there should be away to optimize this ...

ypercubeᵀᴹ Over a year ago

Compound index on (KeywordId, MatchType) would help on table u. Not sure about the other.

ypercubeᵀᴹ Over a year ago

First test that the subquery (with the GROUP BY) calculates what it should calculate. After you are sure, then try to optimize the UPDATE.

th3an0maly Over a year ago

@pocko If this solution worked for you, please consider marking it as the Accepted Answer.

Collectives™ on Stack Overflow

Mysql update query with select with join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related