Mysql update request using select with join

Question

I am trying to run a mysql update request using select in it, but I am getting an error. Inquiry:

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 only get "# 1064 - You have an error in the SQL syntax, check the manual that matches the version of your MySQL server for the correct syntax to use next" (Impressions_chg, Clicks_chg, AveragePosition_chg, Ctr_chg, AverageCpc_chg, Curr 'on line 1 "

Can anyone help me with this?

Thanks!

Answer 1

You cannot have SET (a,b) = (value_a, value_b) in MySQL.

Rewrite the request. Something like that:

 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 ;