>I need to put all available monthly Values from Updates
>to 1 Data record where MyKey and Year are equal.
IOW you want to save the results of the business end of a crosstab
(pivot table) query. The
crosstab analysis will require a full query. MySQL has an INSERT ...
but no UPDATE ... SELECT command, so this will be a two-step. If I
description correctly, you want to aggregate by month and report by
mykey and year, so
your crosstab would look something like this (not tested)...
CREATE TEMPORARY TABLE crosstab
SUM(IF(u.month=1 ,u.value,0)) AS jan,
SUM(IF(u.month=2 ,u.value,0)) AS feb,
... etc ...
SUM(IF(u.month=12,u.value,0)) AS dec)
FROM data AS d
INNER JOIN updates AS u USING (myKey)
GROUP BY mykey,year;
aggregating updates to one row per mykey per year. Then update the data
with something like ...
UPDATE Data AS d INNER JOIN crosstab AS c
ON d.myKey = c.myKey AND d.year = c.year
SET d.Jan = c.jan ... etc ...
Please help me with the following problem in MySQL 5.0.15.
I have 2 MyISAM tables like:
- table Updates with fields myKey, Year, Month, Value
where Month has the values 1 .. 12
- table Data with fields myKey, Year, Jan, Feb, ... Dec
I need to put all available monthly Values from Updates
to 1 Data record where MyKey and Year are equal.
I tried the following query:
UPDATE Data AS db INNER JOIN Updates AS U
ON db.myKey = U.myKey
SET db.Jan = IF(U.Month = 1, U.Value, db.Jan),
db.Feb = IF(U.Month = 2, U.Value, db.Feb),
db.Dec = IF(U.Month=12, U.Value,db.Dec);
But this query takes only the first available Month in Updates,
and ignores the other months per myKey / Year combination.
I would appreciate your help.
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006