List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 28 2006 8:40pm
Subject:Re: UPDATE from monthly to yearly rows
View as plain text  
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
>   

Attachment: [text/html]
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
Thread
UPDATE from monthly to yearly rowsC.R.Vegelin28 Feb
  • Re: UPDATE from monthly to yearly rowsPeter Brawley28 Feb
  • Re: UPDATE from monthly to yearly rowsC.R.Vegelin2 Mar