How to Update with subquery in PostgreSQL

Question

I have a function in MS SQL Server just like this:

UPDATE r
SET
    monthly =
(
    SELECT SUM(-h.value_ini - h.purchase + h.sold + h.value_fin)
    FROM   hist_portfolio AS h
    WHERE  h.comp_id = r.comp_id
     AND h.port_id = r.port_id 
     AND h.exte_id = r.cate_id
     AND h.type_id = @type_rel_aux
     AND h.hcar_day > @date_month_before
     AND h.hcar_day <= @date_base
)
   FROM   #Month_Table r
   WHERE  type = 1;

and thats the result (after update):

    Seq     monthly 
   2        102471,34
   1        -5129,46
   3        -29841,23
   4        0

But when I execute the same update in a fuction in PostgreSQL, all the rows get the same value:

UPDATE Month_Table
    SET variacao_mes_rs = (
SELECT SUM(-h.value_ini - h.purchase + h.sold + h.value_fin)
FROM   hist_portfolio AS h
WHERE  h.comp_id = r.comp_id
 AND h.port_id = r.port_id 
 AND h.exte_id = r.cate_id
 AND h.type_id = v_type_rel_aux
 AND h.hcar_day > v_date_month_before
 AND h.hcar_day <= v_date_base)     FROM   Month_Table r   WHERE  type = 1;

Result (after update), all the same value of Seq 3:]

Seq     monthly 
1   -29841,23
2   -29841,23
3   -29841,23
4   -29841,23

I don't see the cause of the problem... Does PostgreSQL have different rules on UPDATE? Can anyone help me?

Gordon Linoff · Accepted Answer · 2020-09-10 21:24:55Z

1

Remove the FROM clause from Postgres:

UPDATE Month_Table r
    SET variacao_mes_rs = (
SELECT SUM(-h.value_ini - h.purchase + h.sold + h.value_fin)
FROM   hist_portfolio AS h
WHERE  h.comp_id = r.comp_id
 AND h.port_id = r.port_id 
 AND h.exte_id = r.cate_id
 AND h.type_id = v_type_rel_aux
 AND h.hcar_day > v_date_month_before
 AND h.hcar_day <= v_date_base)  
WHERE  type = 1;

The FROM clause in an UPDATE behaves differently in the two databases, as you have discovered.

answered Sep 10, 2020 at 21:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

javaMan Over a year ago

You're correct! I'm so used to the MSSQL syntax that I would never realize. Thank you!

javaMan Over a year ago

How can I do an update with join in PostgreSQL? In this case I must have a FROM clause, right?

Gordon Linoff Over a year ago

@javaMan . . . The subquery is more convenient because of the aggregation.

Gordon Linoff Over a year ago

@javaMan . . . I don't understand. The subquery is never returning more than one row. It is an aggregation query with no GROUP BY.

javaMan Over a year ago

Sorry.. I confused with other problem. It worked in this case.

Collectives™ on Stack Overflow

How to Update with subquery in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related