Re: SQL Update Help Needed

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

Seems strange that you ultimately want to store amounts that are entirely derivable from the names; however...

> 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

I'm going to assume that all amounts that are non-default (non-zero) value are the same for any one value of name, ie. for any one value of name, there are two possible values for amount; the table-wide default value (zero) or the "special" value particular to that name. If this is not the case, then how do you know Which value to set any currently-default-value(zero) amount to?

> How do I do this in a single update statement?
> update table set amount=(select amount from table where ... ) where ... ;
> ???

Try

update table a set amount=(select distinct amount from table where name=a.name

                           and amount != 0) where amount=0;

Note you need the distinct, otherwise the subquery will return a whole array of values for amount, even though they'll all be the same, and you'd get error ora 1427.

> Thanks.

You're welcome, hope this helps; if not, then I suspect you're missing something from the explanation.

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

Hank Robinson
Oracle DBA
University of Warwick Received on Sat Aug 20 1994 - 15:17:21 CEST