Re: SQL Update Help Needed

In article <330e7a$nbo_at_delphinium.cig.mot.com> mcrae_at_rtsg.mot.com (Denis M. McRae) writes:
>I have a table something like this:
>
> (name,location,amount) where the key is (name,location).
>
>Here's my problem. New records get entered with different default a value for
>amount. At some later time I want to update these new records from existing
>ones to make the amounts match. For example:
>
> name location amount
> ---- -------- ------
> A here 0
> A there 30
> A elsewhere 0
> B hither 0
> B yon 200
> B hill 0
> B dale 200
>
>Now, I want to update the table to make the zero amounts for A and B match the
>non-zero amount already in the table. After the update I want:
>
> name location amount
> ---- -------- ------
> A here 30
> A there 30
> A elsewhere 30
> B hither 200
> B yon 200
> B hill 200
> B dale 200
>
>How do I do this in a single update statement?
>
>update table set amount=(select amount from table where ... ) where ... ;
>
>???
>
>Thanks.
>--
>-Denis McRae (IL75-2L9)----.email:mcrae_at_rtsg.mot.com TEL/FAX:(708)632-7069/2414
>| Motorola, Inc. (CIG) |cliche: Individuals making their abode in vitreous
>| 1475 West Shure Drive | edifices would be advised to refrain from
>`-Arlington Heights,IL 60004' catapulting petrous projectiles.

If you want the columns with 0 amount to be updated with the highest value for that key then you could use

Update table_name a set amount =
(select max(amount) from table_name b where b.name = a.name) where amount = 0
/

Change the sql to select the min(amount) or sum(amount) as per your requirements

Ramesh Krishnamurthy Received on Mon Aug 22 1994 - 22:35:24 CEST