CODEWITHSUNDEEP
sqlgoogle-bigqueryansi-sql
javacash
javacash

Reputation: 173

Filling in Blanks for a Column Based on Other Column in BigQuery Standard SQL

I have data about artists/singers and for each of them, I have the following columns:

fb

inst

tw

sound

soundcloud_id

facebook_id

instagram_id

twitter_id

To clarify, 'fb' means Facebook, 'inst' means Instagram, 'tw' means Twitter, and 'sound' means Soundcloud.

There are some rows (or artists) in which for instance, 'fb' is filled but 'facebook_id' is null. Likewise, I also have some rows in which 'fb' is null but 'facebook_id' is filled. This also applies to the other columns as well.

My goal is to, for each artist, update the 'fb', 'tw', 'sound' and 'inst' column by filling in the nulls with the corresponding column values of 'facebook_id', 'twitter_id', 'soundcloud_id', and 'instagram_id'.

For example, if I have:

inst instagram_id

NULL example456

I want:

inst instagram_id

example456 example456

NOTE: I do not want to use UPDATE [COLUMN]. The reason is that this table was created just by some subqueries and data manipulation using data from my database. In other words, I have not actually stored my current table so I cannot use UPDATE [COLUMN]. How would I do this without UPDATE?

Upvotes: 1

Views: 1689

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  IFNULL(fb, facebook_id) fb,
  IFNULL(inst, instagram_id) inst,
  IFNULL(tw, twitter_id) tw,
  IFNULL(sound, soundcloud_id) sound,
  soundcloud_id,
  facebook_id,
  instagram_id,
  twitter_id
FROM `project.dataset.table`

Upvotes: 2

Related Questions