BigQuery How to update multiple numeric columns by the same rule?

Question

Is there any way to update multiple numeric columns by the same rules?

Before:

After: Numeric columns multiple 10

My Query:

select id, (sales*10) as sales, (spend*10) as spend, product
from table

But I have many columns in one table, is there any other simple way to update the data?

Answer 1

I'd like to update all the numeric columns, which data type is int or float. (data multiple 10)

Use below

execute immediate (
  select 'update project.dataset.your_table set ' || 
    string_agg(column_name || ' = 10 * ' || column_name, ', ') || 
    ' where true'
  from `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  where table_name = 'your_table'
  and data_type in ('INT64', 'FLOAT64')
)     

your_table before update (select * from project.dataset.your_table)

your_table after update

However, I don't expect to update the original table. I only want the result.

execute immediate (
select 'select *  replace(' || 
  string_agg('10 * ' || column_name || ' as ' || column_name, ', ') || 
  ') from project.dataset.your_table'
from `project.dataset.INFORMATION_SCHEMA.COLUMNS`
where table_name = 'your_table'
and data_type in ('INT64', 'FLOAT64')
)

How could I get the result data?

After above is run - you should click on VIEW RESULTS