Correlation Clause

Description

Can be used within search-condition or assignment-clause to designate a table, view, nickname, or fullselect.

Include-columns:

Specifies a set of columns that are included, along with the columns of table-name or view-name, in the intermediate result table of the UPDATE statement when it is nested in the FROM clause of a fullselect.

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

Snowflake does not support the addition of Columns in the correlation clause

Grammar Syntax

Sample Source Patterns

IBM DB2

/* Example with out include-columns*/
UPDATE TABLE1 AS t1
     SET t1.Column1 = 1;
     
/* Example with include-columns*/	
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

/* Example with out include-columns*/
UPDATE PUBLIC.TABLE1 AS t1
    SET t1.Column1 = 1;

/* Example with include-columns*/
-- ** MSC-ERROR - MSCEWI5006 - INTERMEDIATE RESULT TABLE IS NOT SUPPORTED. **
--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'

Related EWIs

1. MSCEWI5006: INTERMEDIATE RESULT TABLE IS NOT SUPPORTED.