Comment on page
MSCEWI1075
Current of clause is not supported in Snowflake
High
Some statements like UPDATE and DELETE can use have a CURRENT OF clause inside the WHERE clause, this is not currently supported by Snowflake.
CREATE OR REPLACE PROCEDURE proc_update_current_of
AS
CURSOR C1
IS
SELECT * FROM F_EMPLOYEE FOR UPDATE OF SALARY nowait;
BEGIN
FOR CREC IN C1
LOOP
UPDATE F_EMPLOYEE SET SALARY=SALARY+2000 WHERE CURRENT OF C1;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE PUBLIC.proc_update_current_of ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
C1 CURSOR FOR
SELECT * FROM PUBLIC.F_EMPLOYEE FOR UPDATE OF SALARY nowait;
BEGIN
FOR CREC IN C1 DO
-- ** MSC-ERROR - MSCEWI1075 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
-- UPDATE F_EMPLOYEE SET SALARY= /*** MSC-WARNING - MSCEWI3036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/
--SALARY+2000 WHERE CURRENT OF C1
;
END FOR;
END;
$$;
- Redesign the query to normal
UPDATE
orDELETE
specifying the columns in theWHERE
clause, consider that if there are duplicate records in the table the query can affect them multiple times.