BigQuery: update a column if a record exists in other tables

Question

A client has multiple rows in both table1 and table2. I want to update a boolean, table1.email_flag, to true for every row for a client, if that client has one or more rows with email_flag=true in either table1 or table2 (or both).

This SQL does it:

update table1 a
  set email_flag = true
  from
    (select client_id
    from table1
    where email_flag = true
    UNION DISTINCT
    select client_id
    from table2
    where email_flag = true) b
  where a.client_id = b.client_id;

But I need to rewrite it without the UNION.

Some sample data:

Table1:

rownum	client_id	email_flag
1	Client1	false
2	Client1	true
3	Client1	false
4	Client2	false
5	Client2	false
6	Client3	true
7	Client3	true
8	Client3	false
9	Client4	false
10	Client4	false

Table2:

rownum	client_id	email_flag
1	Client1	false
2	Client1	false
3	Client1	false
4	Client1	false
5	Client2	false
6	Client2	false
7	Client3	true
8	Client3	false
9	Client3	false
10	Client4	false
11	Client4	true

Desired outcome for Table1:

rownum	client_id	email_flag
1	Client1	true
2	Client1	true
3	Client1	true
4	Client2	false
5	Client2	false
6	Client3	true
7	Client3	true
8	Client3	true
9	Client4	true
10	Client4	true

• All of Client1's rows will be True because it has a True row in Table1
• All of Client2's rows will be False because it doesn't have any True records in either table
• All of Client3's rows will be True because it has True rows in both tables
• All of Client4's rows will be True because it has a True row in Table2

Thanks in advance.

Answer 1

You can use two EXISTS expressions, that check from both tables if there's at least one email_flag set to true for the specific client.

UPDATE table1 a
SET email_flag = true
WHERE EXISTS(SELECT 1 FROM table1 b WHERE email_flag='true' AND a.client_id = b.client_id) 
   OR EXISTS(SELECT 1 FROM table2 b WHERE email_flag='true' AND a.client_id = b.client_id);