I have main table data that consists of medium, network and need to rename the values in 3 columns from a dictionary table by exact matching the values from this 3 columns. How do I do so in bigquery sql?

I currently have a dictionary table which contains data as so:

and the main table as such:

into

So it will match medium, network, name and replace all values with newMedium,newNetwork,newName.

CodePudding user response：

I think this query is what you're after:

SELECT
  COALESCE(dict.newMedium, main.medium) medium
  , COALESCE(dict.newNetwork, main.network) network
  , COALESCE(dict.newName, main.name) name
  , main.date
FROM
  main_table main
  LEFT JOIN dictionary_table dict
  ON (main.medium = dict.medium AND main.network = dict.network AND main.name = dict.name)

Basically, just LEFT JOIN both tables. Doing this, non-matching fields will be NULL. This way, you can use COALESCE to use the newField, if it exists, or the main.field otherwise.