Update column values in Athena

Question

i am trying to make an update operation in AWS Athena for my column value that has doubles quotes.

I tried:

UPDATE "table_name" 
SET user_id = REPLACE(user_id, '"', '')

Is update not supported in Athena?

Answer 1

UPDATE is not available in the Athena instruction set.

https://docs.aws.amazon.com/athena/latest/ug/ddl-sql-reference.html

It is not the purpose of Athena to edit source data.

You could however perform the string replace after the select using replace https://prestodb.io/docs/current/functions/string.html.

replace(string, search, replace) → varchar#
Replaces all instances of search with replace in string.

If search is an empty string, inserts replace in front of every character and at the end of the string.

Eg

SELECT user_id, replace(user_id, '"', '') FROM "table_name";