Thanks Peter,
I didn't know that MySQL has no UPDATE ... SELECT command.
I followed your advice and made the following query
UPDATE Data AS db INNER JOIN
(SELECT myKey,Year,
SUM(IF(Month= 1,Cell,Null)) AS `Jan`,
...
SUM(IF(Month=12,Cell,Null)) AS `Dec`
FROM Updates GROUP BY myKey, Year) AS sq
ON (db.myKey=sq.myKey AND db.Year=sq.Year)
SET db.Jan = sq.Jan, ..., db.Dec = sq.Dec;
This works fine and fast. Thanks again !
Regards, Cor
----- Original Message -----
From: Peter Brawley
To: C.R.Vegelin
Cc: mysql@lists.mysql.com
Sent: Tuesday, February 28, 2006 8:40 PM
Subject: Re: UPDATE from monthly to yearly rows
Cor,
>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 ... SELECT
command,
but no UPDATE ... SELECT command, so this will be a two-step. If I understand
your
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
SELECT
d.myKey,
d.year,
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 table
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 ...
PB
-----
C.R.Vegelin wrote:
Hi List,
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.
TIA, Cor
----------------------------------------------------------------------------
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
------------------------------------------------------------------------------
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
