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!
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 ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With