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.

Hi,

Here is the sample code which has been tried
create table recert
(
  id int,
  parent_id int,
  country_id int,
  name varchar(100)
)


create table c
(
  id int
)
UPDATE o
SET parent_id = o2.ID
    FROM recert O
    JOIN c C ON C.ID = O.country_id
    JOIN recert P ON O.parent_id = P.ID and p.country_id <> O.country_id              
    JOIN recert o2 on o2.name = p.name and c.ID = o2.country_id
    JOIN c c2 on c2.ID = o2.country_id
WHERE O.ID = o2.ID
Hope this helps.

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.
    70,220 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