UPDATE FROM - PostgreSQL to Oracle Migration

In PostgreSQL, you can update rows of a table based on data from other tables using the FROM clause.

PostgreSQL:

  -- Update table T1 based on data from T2 and T3 tables
  UPDATE t1
  SET c1 = t2.c1,
         c2 = 'Text ' || t3.c2
  FROM t2, t3
  WHERE t1.id = t2.id AND t2.id2 = t3.id2;

In Oracle, you have to use UPDATE with a subquery and WHERE EXISTS clause:

Oracle:

  -- Update table T1 based on data from T2 and T3 tables
  UPDATE t1
  SET (c1, c2) = 
         (SELECT t2.c1, 'Text ' || t3.c2
          FROM t2, t3
          WHERE t1.id = t2.id AND t2.id2 = t3.id2)
   WHERE EXISTS
         (SELECT 1
          FROM t2, t3
          WHERE t1.id = t2.id AND t2.id2 = t3.id2)

• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

• PostgreSQL to Oracle Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.