Update Target

Description

Identifies the object of the update operation.

Click here to navigate to the IBM DB2 docs page for this syntax.

Snowflake only allows tables as the object of the update operation. The output query can be malformed.

Grammar Syntax

Sample Source Patterns

IBM DB2

Table Name

UPDATE TABLE1
	SET Column1 = 1;

Only

UPDATE ONLY(TABLE2) 
	SET Column1 = 1;

Fullselect

UPDATE (SELECT EMPNO, SALARY, COMM,
     AVG(SALARY) OVER (PARTITION BY WORKDEPT),
     AVG(COMM) OVER (PARTITION BY WORKDEPT)
     FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
   SET (SALARY, COMM) = (AVGSAL, AVGCOMM)
   WHERE EMPNO = '000120';

Snowflake

Table Name

UPDATE TABLE1
	SET Column1 = 1;

Only

UPDATE/*** MSC-WARNING - MSCEWI5004 - ONLY TABLE REFERENCE IS NOT APPLICABLE IN SNOWFLAKE. ***/  TABLE2
	SET Column1 = 1;

Fullselect

UPDATE
-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--       (SELECT EMPNO, SALARY, COMM,
--     AVG(SALARY) OVER (PARTITION BY WORKDEPT),
--     AVG(COMM) OVER (PARTITION BY WORKDEPT)
--     FROM EMPLOYEE E)
                      AS E
-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--                          (EMPNO, SALARY, COMM, AVGSAL, AVGCOMM)
     SET
     SALARY = AVGSAL,
     COMM = AVGCOMM
   WHERE EMPNO = '000120';

Related EWIs

1. MSCEWI5004: ONLY OUTER TABLE REFERENCE IS NOT APPLICABLE IN SNOWFLAKE.
2. MSCEWI5006: INTERMEDIATE RESULT TABLE IS NOT SUPPORTED.