CODEWITHSUNDEEP
sqlpostgresql
Pablo Lozano
Pablo Lozano

Reputation: 10342

How to update field with value from other table with several joins?

I have the following tables, all of them with an auto-incremental ID:

So my problem is some prices have been updated, but there was a typo in some of them and now I need to set the prices in some delivery notes. I can list what I want to do using:

select dn.id as "note ID",
  dne.id as "entry",
  dne.product_id "product ID",
  dne.base_price as "current price",
  pp.price as "to be price"
from product_price pp
join delivery_note_entry dne on dne.product_id = pp.product_id 
join delivery_note dn on dn.id=dne.delivery_note_id 
join customer c on dn.customer_id = c.id
join customer_category cc on cc.id = c.customer_category_id 
where dn.creation_date between '2020-08-28' and '2020-08-29'

But I am not able to build the update query to change prices. I've tried

update delivery_note_entry dne set base_price = pp.price 
from product_price pp
join delivery_note dn on dn.id = delivery_note_id 
join customer c on dn.customer_id = c.id
join customer_category cc on cc.id = c.customer_category_id_2 
where dne.product_id = pp.product_id 
and dn.creation_date between '2020-08-28' and '2020-08-29'

But I get the following error using DBeaver:

SQL Error [42703]: ERROR: column "delivery_note_id" does not exist
  Hint: There is a column named "delivery_note_id" in table "dne",
but it cannot be referenced from this part of the query.

Upvotes: 0

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

If I understand correctly, most of the conditions are for filtering. Hence, I think this does what you want:

update delivery_note_entry dne
    set base_price = (select pp.price from product_price pp where dne.product_id = pp.product_id)
from delivery_note dn join
     customer c
     on dn.customer_id = c.id join
     customer_category cc
     on cc.id = c.customer_category_id 
where dn.id = dne.delivery_note_id and
      dn.creation_date between '2020-08-28' and '2020-08-29';

EDIT:

Based on the comment, you don't need a subquery:

update delivery_note_entry dne
    set base_price = pp.price
from delivery_note dn join
     customer c
     on dn.customer_id = c.id join
     customer_category cc
     on cc.id = c.customer_category_id join
     product_price pp
     on pp.customer_category_id = c.customer_category_id
where dn.id = dne.delivery_note_id and
      dne.product_id = pp.product_id and
      dn.creation_date between '2020-08-28' and '2020-08-29';

Upvotes: 1

Koushik Roy
Koushik Roy

Reputation: 7387

can you pls try -

UPDATE delivery_note_entry dne 
set  dne.base_price =(
select pp.price 
from product_price pp
join delivery_note dn on dn.id=dne.delivery_note_id 
join customer c on dn.customer_id = c.id
join customer_category cc on cc.id = c.customer_category_id 
where 
dn.creation_date between '2020-08-28' and '2020-08-29'
)
where dne.product_id = pp.product_id and

Upvotes: 0

Related Questions