List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:March 2 2006 11:15am
Subject:Re: UPDATE from monthly to yearly rows
View as plain text  
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@stripped 
  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


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