CODEWITHSUNDEEP
google-bigqueryupdateswhere-clause
TigSh
TigSh

Reputation: 645

Update Table with Aggregate function in where clause Bigquery

I have TableA with the following data

 Name   A   B   Final
 Andy   1   1   2 
 Sam    1   0   2

I want to write an update query which will do the following:

  1. If sum(A+B) <> Final then update row else do not Update

My Query 

Update TableA
Set A = Case when Final in (1,2) then 1 else 0 End
Set B = Case When Final = 2 then 1 else 0
where final in (1,2) and **final <> sum(A+B)**

Since we can't use an aggregate function in update where clause I am not sure how to do the last part.

The query should only update row for Sam.

Thanks for you help!

Upvotes: 0

Views: 360

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Update TableA
Set A = Case when Final in (1,2) then 1 else 0 End
Set B = Case When Final = 2 then 1 else 0
where final in (1,2) and final <> A+B

Upvotes: 1

Related Questions