Snowflake Update Syntax

0
votes

This is my first post on Stackoverflow, I hope I'm in-bounds with the rules. I'm currently going through the Snowflake DB and I'm trying to update a table using "UPDATE", but I receive this error SQL compilation error: syntax error line 6 at position 0 unexpected 'UPDATE'. syntax error line 7 at position 38 unexpected ') I've already checked the syntax format and special characters. Could someone assist? (I'm a novice)

Select Replace (Fdgrp_cd,'~','') as Fdgrp_cd

from "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST"


UPDATE "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST"

SET Fdgrp_cd = REPLACE(fdgrp_cd,'~','')
2
sqlsyntaxsnowflake-cloud-data-platform
Is your UPDATE works alone? remove entire SELECT query and execute only UPDATE – Popeye
I removed the Select, but I would need that because I'm trying to replace the '~' within my data table permanently. It has something to do with my UPDATE function. – MacroLearner
If you want to permanantly change the value in table then you dont need select. Update will do permenant change in your table with replace function. – Popeye
Replace (Fdgrp_cd,'~','') as Fdgrp_cd from "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST" UPDATE "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST" SET Fdgrp_cd = REPLACE(fdgrp_cd,'~','') Something Like this? – MacroLearner

2 Answers

0
votes
UPDATE "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST"
SET fdgrp_cd = REPLACE(fdgrp_cd,'~','')

This statement should work all by itself. The issue you are having is because you are trying to execute both statements at the same time in the UI, which only allows 1 query at a time. Separate your queries with ; to delineate the queries so Snowflake UI understands that these are 2 separate queries.

0
votes

Just run the update by itself (although a semicolon between the expressions might also work).

Often, it is more efficient in an update to only update the rows that need to be updated. For this, you would use a where clause:

UPDATE "USDA_NUTRIENT_STDREF_01"."PUBLIC"."FD_GROUP_INGEST"
    SET Fdgrp_cd = REPLACE(fdgrp_cd, '~', '')
    WHERE fdgrp_cd LIKE '%~%';