Thanks, Roger
You pointed me to the right direction.
The only part I had to change, was using the SET term once, as below.
UPDATE Data INNER JOIN Updates ON ...
SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan),
Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb),
Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), etc.
And it works much faster than 12 separate queries.
Regards, Cor Vegelin
----- Original Message -----
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Cc: "C.R.Vegelin" <[EMAIL PROTECTED]>
Sent: Thursday, November 17, 2005 11:53 PM
Subject: Re: dynamic fieldname to assign to
C.R.Vegelin wrote:
Hi All,
I have a "simple" problem and hope you can help me.
I have an input table Updates with various fields, incl. Month and
MonthlyValue.
The field Updates.Month ranges from 1 to 12.
I also have a target Data table with 12 fields, called Jan, Feb, Mar, ...
`Dec`.
Depending on Updates.Month the MonthlyValue must be put in the proper
Data field.
Now I use 12 UPDATE queries, like:
UPDATE Data INNER JOIN Updates ON ...
SET Data.Jan = Updates.Cell WHERE Updates.Month = 1;
...
UPDATE Data INNER JOIN Updates ON ...
SET Data.Dec = Updates.Cell WHERE Updates.Month = 12;
My question: can it be done in a single query ?
Try something like this:
UPDATE Data INNER JOIN Updates ON ...
SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan),
SET Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb),
SET Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar),
...
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
