Re: UPDATE from monthly to yearly rows

Peter Brawley Tue, 28 Feb 2006 12:41:29 -0800

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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: UPDATE from monthly to yearly rows

Reply via email to