Postgres update multiple row in one action

Question

I have two tables

app_detail_quot_s
-----------+--------------+
part_id    +unit_price    +
-----------+--------------+
1          +    100.000   +
2          +    200.000   +
3          +    300.000   +


app_supp_po_dt
-----------+--------------+
part_id    +unit_price    +
-----------+--------------+
1          +    null      +
2          +    null      +
8          +    null      +

the result after query update executed is

app_supp_po_dt
-----------+--------------+
part_id    +unit_price    +
-----------+--------------+
1          +    100.000   +
2          +    200.000   +
8          +    null      +

how to UPDATE all unit_price when part_id between app_detail_quots and app_supp_po_detail is equal in just one action using PostgreSQL?

I am trying this code:

update app_supp_po_dt set
    unit_price =
 ( 
    select unit_price from app_detail_quot_s a left join app_supp_po_dt b on    a.part_id= b.part_id
 )

But I get error :

more than one row returned by a subquery used as an expression

user330315 · Accepted Answer · 2015-09-02 08:57:38Z

1

You don't need the join inside the sub-select, just make it a regular co-related sub-select:

update app_supp_po_dt 
 set unit_price = (select unit_price 
                   from app_detail_quot_s a 
                   where a.part_id = app_supp_po_dt.part_id);

The above assumes that part_id is unique in both tables.

The co-related sub-query will return null if a part_id is not found in app_detail_quot_s and will overwrite any value stored in app_supp_po_dt in that case, if you do not want that, you need to exclude rows from app_supp_po_dt that have a unit price but do not appear in app_detail_quot_s

update app_supp_po_dt 
 set unit_price = ( select unit_price from app_detail_quot_s a where a.part_id = app_supp_po_dt.part_id)
where unit_price is null
  and exists (select 1 
              from app_detail_quot_s a2 
              where a2.part_id = app_supp_po_dt.part_id);

Another (non-standard) option is to use a join in the update statement, which makes the statement a bit more readable:

update app_supp_po_dt 
  set unit_price = a.unit_price
from app_detail_quot_s a
where a.part_id = app_supp_po_dt.part_id;

This is non-standard SQL and will not work on other DBMS, but is most probably much faster than the solutions with a co-related sub-query

answered Sep 2, 2015 at 8:57

user330315

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

3 Comments

Alimin Over a year ago

thank for your tips @a_horse_with_no_name, but I am sucees trying my own query who relevant in my case this is my newer query in my real case update app_supp_po_dt set unit_price= ( select price from app_detail_quot_s a left join app_supp_po_dt b on a.part_id= b.part_id left join app_quots c on a.quot_s_id= c.id left join app_supp_po d on c.id=d.quots_id where a.part_id=b.part_id ) thank U

user330315 Over a year ago

@Alimin: do not post code in comments. Edit your question - but don't change the question completely. If you have a new question, then ask a new question

Alimin Over a year ago

I just want helping someone when they have some problem like me in future, in other time I use your advice ok, please approve my question -_-

Collectives™ on Stack Overflow

Postgres update multiple row in one action

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related