Nested query SQL

Question

I have a quote_item table where each item has the following fields: name, product_id, r_code1, r_code2

Product table also has r_code1 and r_code2

That data is in two locations because the user wanted quotes to be a snapshot, if they change r_code1 later at the product level it shouldn't impact quotes automatically.

However I've now been asked to reflect r_code changes to existing quotes since there was a mistake.

If I have a quote with 20 quote_items, how would I write a query that updates each quote_item.rcode_1 to the corresponding product.rcode_1. The link is through quote_item.product_id = product.id.

Thanks in advance Nick

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2015-07-19 07:12:21 +00:00
Commented Jul 19, 2015 at 7:12 — user330315
– user330315, Commented Jul 19, 2015 at 7:12
postgres, sorry to leave that out.

nktokyo
– nktokyo

2015-07-21 00:36:40 +00:00
Commented Jul 21, 2015 at 0:36 — nktokyo
– nktokyo, Commented Jul 21, 2015 at 0:36

Tom Studee · Accepted Answer · 2015-07-19 03:44:30Z

2

update qi set qi.rcode_1 = product.rcode_1 
from quote_items qi
    inner join product 
        on product.id = qi.product_id

answered Jul 19, 2015 at 3:44

Tom Studee

10.5k4 gold badges42 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-07-19 13:35:59Z

1

The following is standard SQL that would work in any database:

update qi
    set rcode_1 = (select p.rcode_1 from product p where p.id = qi.product_id);

There are other ways to express this, depending on the database, but the above should work in any database.

answered Jul 19, 2015 at 13:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Nested query SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related