[prev in list] [next in list] [prev in thread] [next in thread]
List: mysql
Subject: Re: UPDATE from monthly to yearly rows
From: Peter Brawley <peter.brawley () earthlink ! net>
Date: 2006-02-28 20:40:58
Message-ID: 4404B55A.9040008 () earthlink ! net
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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 #5 (text/html)]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Cor,<br>
<br>
>I need to put all available monthly Values from Updates <br>
>to 1 Data record where MyKey and Year are equal.<br>
<br>
IOW you want to save the results of the business end of a crosstab
(pivot table) query. The <br>
crosstab analysis will require a full query. MySQL has an INSERT ...
SELECT command,<br>
but no UPDATE ... SELECT command, so this will be a two-step. If I
understand your<br>
description correctly, you want to aggregate by month and report by
mykey and year, so<br>
your crosstab would look something like this (not tested)...<br>
<br>
CREATE TEMPORARY TABLE crosstab<br>
SELECT <br>
d.myKey,<br>
d.year,<br>
SUM(IF(u.month=1 ,u.value,0)) AS jan,<br>
SUM(IF(u.month=2 ,u.value,0)) AS feb,<br>
... etc ...<br>
SUM(IF(u.month=12,u.value,0)) AS dec)<br>
FROM data AS d <br>
INNER JOIN updates AS u USING (myKey)<br>
GROUP BY mykey,year;<br>
<br>
aggregating updates to one row per mykey per year. Then update the data
table<br>
with something like ...<br>
<br>
UPDATE Data AS d INNER JOIN crosstab AS c<br>
ON d.myKey = c.myKey AND d.year = c.year<br>
SET d.Jan = c.jan ... etc ...<br>
<br>
PB<br>
<br>
-----<br>
<br>
C.R.Vegelin wrote:
<blockquote cite="mid000b01c63c55$d28d2530$0100000a@your0j166cfae3"
type="cite">
<pre wrap="">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
</pre>
<pre wrap="">
<hr size="4" width="90%">
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
</pre>
</blockquote>
</body>
</html>
--------------030206030003080002040404--
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/mysql?unsub=mysql@progressive-comp.com
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic