CODEWITHSUNDEEP
sqlbigdatagoogle-bigquery
Teja
Teja

Reputation: 13524

How to search for a particular value in a column of values and replace with that particular value in Big Query SQL?

How can I search for a particular value 'xyz' in a column of values at id level and replace all of them with 'xyz' at id level if it's found. Here is my sample input/output as below.

****
 - Input :-
****

id,value 
123,'abc'
123,'abc'
123,'xyz'
456,'def'
456,'def'
456,'def'

Output:-

id,value 
123,'xyz'
123,'xyz'
123,'xyz'
456,'def'
456,'def'
456,'def'

Upvotes: 1

Views: 96

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

The way I interpreted the example - you look for all id's where value is 'xyz', and change the value for such ids to 'xyz'. Then the following LEFT JOIN should do it:

SELECT a.id, IF(b.value IS NULL, a.value, 'xyz') FROM (
SELECT a.id, a.value, b.value FROM T a
LEFT OUTER JOIN
(SELECT id FROM T WHERE value = 'xyz') b)

Upvotes: 1

Related Questions