CODEWITHSUNDEEP
google-bigquery
James Harrington
James Harrington

Reputation: 103

Check if value exists in group and assign it to all rows of group in BigQuery

I would like to check if the value a exists in each user id group and then assign this value to all rows of this user id/group. For example for the following data:

user_id type    
    123    a
    123    b
    234    c

I should get:

user_id type    
    123    a
    123    a
    234    c

Upvotes: 0

Views: 751

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

select *, 
  if(0 = countif(type = 'a') over(partition by user_id), type, 'a') as assigned_type,
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Daniel Zagales
Daniel Zagales

Reputation: 3034

You can achieve this by executing a query similar to this:

UPDATE so_test.a_updates
SET type = 'a'
where user_id in (
  select user_id
  from `elzagales.so_test.a_updates`
  where type = 'a'
);

additionally if this is something you want to repeatedly do you can make this a stored procedure using a similar structure.

Upvotes: 0

Related Questions