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;
$$;
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;
...