Re: Help with UPDATE

Enrique wrote:

> I have two tables like these:
>
> Customer_Table
> ----------------------------
> Name Varchar2(20)
> Address Varchar2(20)
> Telephone Number
> Price Number
> Final_Price Number
>
> Discount_Table
> -----------------------------
> Name Varchar2(20)
> Address Varchar2(20)
> Telephone Number
> Discount Number
>
> I want to update the Final_Price column in the Customer table by
> joining both tables by Name, Address and Telephone and multiplying
> Price with Discount.
>
> I tried:
>
> Update Customer_Table a
> set a.final_price = a.price * (select b.discount from discount_table b
> where a.name = b.name and
> a.address = b.address and
> a.telephone = b.telephone)
>
> This query was running for a long time and I killed it. I also suppose
> this query will update ALL rows in the Customer_Table with the same
> value since there is no WHERE clause in the Update.
>
> How can I do this??!!

As this is school work here are two hints.

1. Your WHERE clause limits the rows selected but does not limit the rows updated.
2. What indexes exist on these tables? -- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace 'x' with 'u' to respond)