2

Given a table containing col_1 through col_50 how would I change this query to return new_col and col_1 through col_49?

select tmp_col + 10 as new_col, *
from (
    select col_1 / col_2 + col_3 as tmp_col, *
    from table
) t

I don't want tmp_col and col_50. This query needs to run on several tables with slightly varying names so I need to use the star instead of listing out the column names.

Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
wipphd
  • 111
  • 2
  • 4
  • 1
    There probably is no way to do what you want. Just list out the column names. Even if there were some way to select all columns minus one, the logic might break if a column were added to/removed from the table at a later date. – Tim Biegeleisen Jul 11 '19 at 01:39
  • Possibly create a view to avoid making changes at multiple place but otherwise there might not be a straight forward solution – Raj Jul 11 '19 at 01:43
  • There indeed is no way to do this in SQL. How are you running this queries? By hand or programmatically? Can you do query generation in the host language (assuming not by hand)? – Piotr Findeisen Jul 11 '19 at 05:48
  • 1
    Does this answer your question? [SQL exclude a column using SELECT \* \[except columnA\] FROM tableA?](https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) – EnverOsmanov Apr 06 '21 at 15:08
  • @EnverOsmanov Your mentioned syntax only works in BigQuery and not in AWS Athena. – armiro Aug 17 '22 at 00:59

0 Answers0