Update Statement in db2

[PatelRam]

Friends,


table A

Product_id Product_Category PRICE

1 A 10
2 A 2
3 A 3
4 B 23
5 C 12
6 D 56
7 A 23
8 A 23
9 C 34
10 D 67


table B

Product_id Product_Category PRICE Expected Result for Price

1 A 0 10
2 A 0 2
3 A 0 3
4 B 0
5 C 0
6 D 0
7 A 0 23
8 A 0 23
9 C 0
10 D 0




I have a two table A & B. I would like to update table B column Price with table A column Price
for Product category A. What would be my update statement ???


Thanks, in advance.

Ram

 

[ddiamond]

update b
set price = (
  select price
  from a
  where a.product_id = b.product_id
    and a.category = b.category)
where category = 'A'
  and exists (
    select price
    from a
    where a.product_id = b.product_id
      and a.category = b.category);

You can replace the exists clause with an in clause. I generally find that exists clauses are faster than in clauses.