Migrating Oracle to SQL Server using SSMA–Error O2SS0293: Columns list in set clause cannot be converted

This blog post describes SSMA error message when converting Oracle PL/SQL UPDATE statement with column group assignment.

Oracle PL/SQL allows you to perform multi column update through sub-query. Consider the following example:

CREATE TABLE ACCOUNT
   (
       ACCOUNT_ID NUMBER NOT NULL,
       ACCOUNT_OWNER VARCHAR2(30) NOT NULL
   );

UPDATE ACCOUNT
    SET (ACCOUNT_ID, ACCOUNT_OWNER) = (SELECT 1, 2 FROM dual)
    WHERE ACCOUNT_ID = 10;

SSMA does not support converting UPDATE statement with sub-query and the following conversion error message is issued:

O2SS0293: Columns list in set clause cannot be converted.

The above statement can be converted to T-SQL statement such as the following:

UPDATE    acct
SET            acct.ACCOUNT_ID = updtqry.col1,
                  acct.ACCOUNT_OWNER = updtqry.col2
FROM       ACCOUNT acct, (SELECT 1 col1, 2 col2) updtqry
WHERE    acct.ACCOUNT_ID=10;