How to find the lowest value from several fields and assign it to another field?

[raamay]

i have a table where the records are in the following fashion:

firm                  w1        w3          w4           class
abc ltd.              b          c          d            b
bcd ltd.              c          c          d            c
cde ltd.              a          d          b            a
def  ltd.             d          d          d            d

as clearly indicated, the highest category among w1,w3 and w4 makes up the class. Here "a" is regarded as the highest category and "b" the next and so on. So among the three categories, i want to take the highest category and assign it to class. I have around 2000 records to which i want an update sql statement so that the each records are assigned with proper class. At present the records in class column is empty in my table.

pls help me to achieve this

[SLauren]

SELECT LEAST(ASCII(w1),ASCII(w3),ASCII(w4)) into value FROM Table;

Then convert "value" into charcter again and assign that value to your class field.

SET class = CHAR(val);

you can do like this:
UPDATE Table SET class = CHAR(LEAST(ASCII(w1),ASCII(w3),ASCII(w4)));

Thanks,
Lauren

[raamay]

@SLauren
thankyou Lauren, as your approach for the same seems to work but still i have a problem. That is, i forgot to mention that the w1, w3 and w4 can also contain NULL values. So, if i update it with the above SQL statement, some records are not assigned any class since some categories have "NULL" as values in my mysql table. So, now how should i take care of this NULL problems too?

[SLauren]

UPDATE Table SET class = CHAR(LEAST(IFNULL(ASCII(w1),ASCII('z')),IFNULL(ASCII(w3),ASCII('z')),IFNULL(ASCII(w4),ASCII('z')));

As we can assume that nothing can be greater than 'z' in the alphabet series :)

Thanks,
Lauren

[Atli]

You don't really need the ASCII calls tho. The LEAST function compares characters just the same without them ;-)

[SLauren]

Thanks for the optimization, i became little bit more logical :-)

Thanks,
Lauren

[raamay]

@SLauren
there is some problem with the sql statement cause i get the following error. Sorry, i am not good with this type of sql queries. so please help me further.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I am trying to run this query via phpmyadmin.

[raamay]

@Atli
thankyou for a change of the thread heading

[SLauren]

UPDATE Table SET class = LEAST(IFNULL(w1,'z'),IFNULL(w3,'z'),IFNULL(w4,'z') );

Sorry i forgot to put a mysterious closing bracket at the end in the above query.

This is the optimized query without using ASCII function.

Thanks,
Lauren

[raamay]

forgot to thankyou for your help. it worked like charm .... so thankyou very much.