CODEWITHSUNDEEP
sqlselectprestoamazon-athena
Ali Khan
Ali Khan

Reputation: 134

UPDATING a table which is selected using SELECT query in SQL

I want to use the Update keyword with select something like 

UPDATE(select col1,col2,col3 from UNPIVOTED_TABLE)
SET col1=0
WHERE col1 IS NULL
SET col2=0
WHERE col2 is NULL
SET col3=0
WHERE col3 is NULL

I know my syntax is not right but this basically is what i am trying to achieve

I am selecting 3 columns and there are some null values which i want to update and set it as 0

Also i cannot update the table itself since the original table was UNPIVOTED and i am PIVOTING it in the select statement and i need the pivoted result (that is the columns i have selected) (col1,col2,col3)

Also i am using amazon Athena if that is relevant

Upvotes: 0

Views: 653

Answers (2)

GMB
GMB

Reputation: 222402

If I followed you correctly, you just want coalesce():

select 
    coalesce(col1, 0) col1,
    coalesce(col2, 0) col2,
    coalesce(col3, 0) col3
from unpivoted_table

colaesce() checks if the first argument is null: if it doesn't, it returns the original value as-is, otherwise it returns the value given a second argument instead.

Upvotes: 2

Tal Klinger
Tal Klinger

Reputation: 51

In case you are using Athena, I can assume you have read only access and cannot really update the original data.

In your case, if you'd like to represent the nulls as 0 you can use `IFNULL(column, 0)

For more information about IFNULL you can read here

Upvotes: 0

Related Questions