Re: How to do this in SQL?

evans_at_rex.pfc.mit.edu (Stephen W. Evans) writes:

> 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.

Try

UPDATE TABLE FOO A SET DATAFIELD = myconst WHERE NOT EXISTS

   ( SELECT NULL

        FROM FOO
        WHERE
           NAMEFIELD = A.NAMEFIELD AND DATAFIELD > A.DATAFIELD

> Email replies are best,

I'm not a great enthusiast of email replies: lots of people ask really interesting questions all time, most of which I'm intersted in hearing the answers to (I'm sure this applies to many other people also). Most people who ask for email responses on the promise that they'll sumarise to the list are never heard from again. I, and, I'm sure, others, like to read the answers/ solutions without having to create more net-clutter by asking for them, and so we can all benefit from the knowledge.

> especially ones flaming me for being
> dense and missing what is probably an obvious solution.

Happily, this is a flame-free newsgroup :-)

> --- 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

Hank Robinson
Oracle DBA
University of Warwick Received on Thu Aug 18 1994 - 13:16:37 CEST