update table select

Hi all,

I've a table in with I've a field called "year".

Now this is not what we want anymore but instead we want a value defining
the "period":

table period
1 before 1914
2 between 1914 and 1918
3 between 1919 and 1945
4 from 1946

for now I've those values in the table I want to update:
id; year; period (default 0)
1; 1950; 0
2; 1987; 0
....

how to upgrade the period column using the year value ?

update mytable set period = (select if(year < 1914),1, if(year between 1914
and 1918),2,if(year between 1919 and 1945),3,4)

unfortunately it doesn't work and all example mon mysql.com are on testing
values, not real values from databases.

Thanks for helping.

Bob

On 6 Aug, 15:08, "Bob Bedford" wrote:
> Hi all,
>
> I've a table in with I've a field called "year".
>
> Now this is not what we want anymore but instead we want a value defining
> the "period":
>
> table period
> 1   before 1914
> 2   between 1914 and 1918
> 3   between 1919 and 1945
> 4   from 1946
>
> for now I've those values in the table I want to update:
> id; year; period (default 0)
> 1; 1950; 0
> 2; 1987; 0
> ...
>
> how to upgrade the period column using the year value ?
>
> update mytable set period = (select if(year < 1914),1, if(year between 1914
> and 1918),2,if(year between 1919 and 1945),3,4)
>
> unfortunately it doesn't work and all example mon mysql.com are on testing
> values, not real values from databases.
>
> Thanks for helping.
>
> Bob

Please do NOT multi post. Cross post if you must bit do NOT multi
post. It just wastes people's time. See: http://www.blakjak.demon.co.uk/mul_crss.htm

I have already posted a follow up to your post in
comp.databases.mysql.