How to do this in SQL?

What I need to do is search table foo which contains the following:

namefield
datafild
otherfield1
otherfield2
otherfieldn

and, for each unique namefield, find the highest value of datafield and replace it with a constant. So far the closest I have been able to figure so far is:

ALTER TABLE FOO SET DATAFIELD = myconst WHERE EXISTS

   ( SELECT NAMEFIELD

        FROM FOO F1, FOO F2
        WHERE 
           (F1.NAMEFIELD = F2.NAMEFIELD) AND MAX(DATAFIELD)

but since it's just a guess based on very little knowledge of SQL it probably won't work.

Email replies are best, especially ones flaming me for being dense and missing what is probably an obvious solution.

• Steve

Stephen W. Evans                GEnie : S.EVANS6
MIT Plasma Fusion Center        #4499 +==)=Caligula==>
evans_at_rex.pfc.mit.edu           C$erve : 73540,504
Work : (617)253-5471            Home : (617)625-8086