List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:February 28 2006 10:57am
Subject:UPDATE from monthly to yearly rows
View as plain text  
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

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