[SOLVED] Update column in table using snowflake scripting SQL

Update column in table using snowflake scripting SQL

I am trying to update price column in table sweatsuits.
Goal is to add up 100 to price column values, when color is 'red' or 'pink'. I have written below code and it throws me error saying:

invalid identifier 'PRICE_NEW' (line 229)

Here is the script I executed:

 CREATE OR REPLACE PROCEDURE sp_up_base_table()
 RETURNS TEXT
 LANGUAGE SQL
 AS
 $$
 DECLARE
price_new number(5,2);
color_or_style_text text;
res RESULTSET;
c1 CURSOR FOR SELECT * FROM sweatsuits WHERE color_or_style IN ('Pink', 'Red');
BEGIN
FOR rec IN c1 DO
color_or_style_text := rec.color_or_style;
price_new := rec.price + 100;
UPDATE sweatsuits SET price = price_new where color_or_style = color_or_style_text;
END FOR;

RETURN 'update success';
END;
$$;

Solution

Using cursor FOR loop is an example of highly ineffective way of updating table which can be done as single update statement:

UPDATE sweatsuits 
SET price = price + 100
WHERE color_or_style IN ('Pink', 'Red');

Inside Snowflake Scripting block variables that are part of SQL statement should be prefixed with ::

FOR ...
   ...
   UPDATE sweatsuits 
   SET price = :price_new 
   where color_or_style = :color_or_style_text;
...