Loop Select and Update PostgreSQL

Question

I'm not finding the error. I need to update a table based on the lowest date for each item by removing duplicates.

FOR temprow IN
   SELECT MIN(orcitem_dtime_inclusao), orcitem_orc_id FROM orcamento_itens GROUP BY orcitem_orc_id ORDER BY orcitem_orc_id ASC
LOOP
  UPDATE orcamentos SET orc_dtime_orcamento = temprow.orcitem_dtime_inclusao WHERE orc_id = temprow.orcitem_orc_id;
END LOOP;

"I'm not finding the error." what error? Does it throw an error? Does it not update the table? Or ain't the update correct? OR something else? What's the problem? — Thomas
– Thomas, Commented May 29, 2021 at 1:23

Gordon Linoff · Accepted Answer · 2021-05-29 01:28:38Z

2

You seem to want:

UPDATE orcamentos o
    SET orc_dtime_orcamento = oi.min_orcitem_dtime_inclusao
    FROM (SELECT orcitem_orc_id,
                 MIN(orcitem_dtime_inclusao) as min_orcitem_dtime_inclusao
          FROM orcamento_itens
          GROUP BY orcitem_orc_id
         ) oi
    WHERE oi.orcitem_orc_id = o.orc_id;

You don't need a cursor or other looping mechanism. You can do this all in a single query.

answered May 29, 2021 at 1:28

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Ezequiel Tavares Over a year ago

Woww. Perfect. Thanks

Collectives™ on Stack Overflow

Loop Select and Update PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest