Assignment Clause

Description

Introduces the assignment of values to column names.

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

The update statement in Snowflake only allows a direct assignation

Grammar Syntax

Sample Source Patterns

IBM DB2

UPDATE EMPLOYEE
     SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
     WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
     
UPDATE EMPLOYEE
     SET (JOB, SALARY, BONUS, COMM) = (NULL, DEFAULT, 0, (SELECT EDLEVEL FROM EMPLOYEE2))
     WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

UPDATE EMPLOYEE EU
    SET (EU.COM, EU.SALARY) = (SELECT ES.SALARY FROM EMPLOYEE ES WHERE ES.WORKDEPT = EU.WORKDEPT)
    WHERE EU.EMPNO = '000120';
    
UPDATE TABLE1 T1
    SET (T1.COL1, T1.COL2, T1.COL3 ) = ((SELECT T2.COL1 FROM TABLE2 T2 WHERE T2.COL2 = T1.COL2), 12);

Snowflake

UPDATE PUBLIC.EMPLOYEE
	SET JOB=NULL, SALARY=0, BONUS=0, COMM=0
     WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

UPDATE PUBLIC.EMPLOYEE
	SET
		JOB = NULL,
		SALARY = DEFAULT,
		BONUS = 0,
		COMM = (SELECT EDLEVEL FROM EMPLOYEE2)
     WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'

UPDATE PUBLIC.EMPLOYEE EU
    SET
-- ** MSC-ERROR - MSCEWI5011 - ASSIGNMENT CLAUSE TYPE IS NOT SUPPORTED IN SNOWFLAKE **
--        (EU.COM, EU.SALARY, TEST) = ((SELECT ES.SALARY FROM
--                PUBLIC.EMPLOYEE ES WHERE ES.WORKDEPT = EU.WORKDEPT), TEST)
    WHERE EU.EMPNO = '000120';

UPDATE PUBLIC.EMPLOYEE EU
    SET
-- ** MSC-ERROR - MSCEWI5011 - ASSIGNMENT CLAUSE TYPE IS NOT SUPPORTED IN SNOWFLAKE **
--        (EU.COM, EU.SALARY) = (SELECT ES.SALARY FROM
--                PUBLIC.EMPLOYEE ES WHERE ES.WORKDEPT = EU.WORKDEPT)
    WHERE EU.EMPNO = '000120';

Related EWIs

1. MSCEWI5011: ASSIGNMENT CLAUSE IS NOT SUPPORTED IN SNOWFLAKE