sqlstored-proceduressnowflake-cloud-data-platformsql-scripts

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

    Related: https://stackoverflow.com/a/75644083/5070879