Re: Help with UPDATE

Here's one way to do it:

update
(select final_price, price, discount
from customer_table, discount_table
where customer_table.name = discount_table.name and customer_table.address = discount_table.address and customer_table.telephone = discount_table.telephone) set final_price = price * discount

I think this method requires the Discount_table to have a primary key, otherwise you'll get ORA-01779 error.

Enrique

aviles94_at_rcn.com (Enrique) wrote in message news:<ca9fa791.0411081940.575054d_at_posting.google.com>...
> 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??!!
Received on Tue Nov 09 2004 - 08:54:12 CST