Multiple Join Update

25 pts.
Tags:
JOIN statement
SQL
SQL queries
UPDATE statement
Hi, my need is to update a table with a join of 2 other tables. The first table (a) has 5 columns (product_id number, ist number, plan number, plan_w number, ist_w number) The second table (b) has 2 columns (prod_group_id number, percent number) The third table (c) has 2 columns (product_id number, prod_group_id number) My intention is to update the columns ist_w and plan_w of the first table (a). a.Plan_w = a.plan * b.percent a.ist_w = .ist * b.percent Every product_id from table a that is in the prod_group of table b shall be updated. Therefor i have to join table b with table c on prod_group_id and table a with table c on product_id. How can I achieve this goal? Thanks in advance!

Answer Wiki

Thanks. We'll let you know when a new response is added.

This is an example of the syntax using Oracle:

UPDATE (
SELECT
a.field_y old,
b.field_y new
FROM table_a a
INNER JOIN table_b b ON a.field_x = b.field_x)
)
SET old = new
WHERE <conditions>;

Please modify it as needed.

There are some restrictions, because you are really updating a view.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    What database (and version) are you using ? The syntax could be different depending on that.
    69,410 pointsBadges:
    report
  • Keltos
    The RDBMS is Oracle 10.2.0.4.
    25 pointsBadges:
    report
  • Keltos
    Thx for your help. I have googled the restrictions but fortunately the are not important for me. With kind regards keltos
    25 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following