Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Set value of one column in the select result

I am trying to replace the value of one of the columns in the SELECT result to 0. Normally I can do:

SELECT
    0 AS column_to_set,
    other_col_1,
    other_col_2,
    ...
FROM tbl

However, there are lots of "other_cols" in the source table, and I wanted to avoid typing them all out.

Is there any way I can utilize SELECT * but achieve the goal of replacing the value for one specific column? The engine is AWS Athena.

Tags:

2

Answers


  1. 0

    try to add alias on your table then do the alias.* on select

    EX:

    SELECT 
0 AS column_to_set,
a.*
FROM tbl AS a
    Login or Signup to reply.
  2. 0

    This is not supported in standard ANSI SQL as far as I know and this is not supported in Trino/Presto. And you can’t dynamically generate AND execute query (i.e. in one query) in Trino/Presto too (see this discussion @github).

    The only "easy" option is to automate this by using information_schema.columns to generate the desired query. It can be done via a separate query (i.e. you write a query which will generate the desired one) or some "outside" scripting. For example imagine we have nation table with columns nationkey, name, regionkey and comment with goal to replace comment. Then the following query:

    select 'SELECT 0 AS new_comment, ' -- column to replace
         || array_join(array_agg(column_name), ', ') -- rest of the column
    || ' FROM nation' as query
from information_schema.columns 
where table_schema = 'tiny' and table_name='nation'
    AND column_name <> 'comment';

    Will result in the following output:

                                  query
-----------------------------------------------------------------
 SELECT 0 AS new_comment, nationkey, name, regionkey FROM nation
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search