UPDATE Statement - IBM DB2 to Oracle Migration

DB2 and Oracle provide the UPDATE statement to update existing rows in a table, but there are differences in the syntax. Assume there is a sample table:

  CREATE TABLE colors
  (
     name VARCHAR(30),
     hex CHAR(7),
     rgb CHAR(11)
   );
 
   INSERT INTO colors VALUES ('Red', NULL, NULL);

In DB2 you can use the following syntax to update multiple columns in a single UPDATE statement:

DB2:

  -- DB2 specific syntax to update multiple columns
  UPDATE colors 
  SET (hex, rgb) = ('#FF0000', '255,0,0') 
  WHERE name = 'Red';

In Oracle, this syntax is only allowed when you assign the new values from a subquery:

Oracle:

  -- This syntax is not supported
  UPDATE colors 
  SET (hex, rgb) = ('#FF0000', '255,0,0') 
  WHERE name = 'Red';
  # ERROR at line 2:
  # ORA-01767: UPDATE ... SET expression must be a subquery

The easiest way to modify the original DB2 query to be compatible with Oracle is to use SELECT FROM dual as follows:

Oracle:

  -- Convert the assignment to a subquery
  UPDATE colors 
  SET (hex, rgb) = (SELECT '#FF0000', '255,0,0' FROM dual) 
  WHERE name = 'Red';
  # 1 row updated.

This conversion approach can be useful if many columns are updated and each assignment value contains a complex expression. But you can also use comma-separated list of assignments (supported in DB2 as well):

Oracle and DB2:

  -- Generally adopted UPDATE syntax
  UPDATE colors 
  SET hex =  '#FF0000', rgb = '255,0,0' 
  WHERE name = 'Red';
  # 1 row updated.

• IBM DB2 to Oracle Migration Tools and Reference
• Online SQL Conversion Tool

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

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2013.