Oracle query not executing in PostgreSQL

Question

I have a query :

update (select tmp."table1" as tmpid, 
               del."table1" as  delid 
          from "table1_TMP" tmp ,
               "table1_DEL" del 
         where del."table2" = tmp."table2" and 
               del."REFSEQNO" = tmp."REFSEQNO")
   set tmpid = delid;

This query runs in oracle without any problem.

How can I modify this query to run on Postgresql?

+1 because I did not know Oracle has updateable inline views. That is cool. — Thilo
– Thilo, Commented Dec 14, 2012 at 12:30
Very efficient too, but limited to views where there are sufficient constraints in place to guarantee that each row in the update target cannot join to multiple rows. There was, and maybe still is, an undocumented optimiser hint BYPASS_UJVC to prevent the checking prior to execution, but it didn't work in at least one version and recently MERGE is preferred where the constraints are not in place. Oracle will also DELETE on a join view -- the child rows are deleted, which makes sense of course. — David Aldridge
– David Aldridge, Commented Dec 15, 2012 at 22:55

Gary - Stand with Ukraine · Accepted Answer · 2012-12-14 12:53:58Z

3

I think this would do it.

update "table1_TMP" tmp
set "table1" = del."table1"
from "table1_DEL" del
where del."table2" = tmp."table2" and
      del."REFSEQNO" = tmp."REFSEQNO"

answered Dec 14, 2012 at 12:53

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

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

1 Comment

Philip Couling Over a year ago

Yup. They key is that postgresql allows a from clause in an update statement. postgresql.org/docs/current/static/sql-update.html

Collectives™ on Stack Overflow

Oracle query not executing in 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

Related