CODEWITHSUNDEEP
google-bigquery
Oksana Ok
Oksana Ok

Reputation: 555

BigQuery update nested table based on condition

This is the code:

UPDATE `xxx.xxx.gift_test_1`
SET PaymentLines = ARRAY(SELECT AS STRUCT * REPLACE('gift_card' AS PaymentType) FROM UNNEST(PaymentLines) WHERE PaymentMethod = 'gift_card')
WHERE true

The problem here that I need to update Payment type to 'gift_card' ONLY where PaymentMethod = to 'gift_card', but my code updates EVERYTHING and PaymentMethod and PaymentType to 'gift_card' after it executed

UPDATE 1.0

UPDATE `xxx.xxx.gift_test_1`
SET PaymentLines = ARRAY(
  SELECT AS STRUCT * REPLACE ('gift_card' AS PaymentType)
  FROM UNNEST(PaymentLines) as pay
)
WHERE EXISTS (select 1 from unnest(PaymentLines) as pay WHERE pay.PaymentMethod = 'gift_card')

This code update all PaymentType to the 'gift_card', so in this cercumstances WHERE clause do not work.

UPDATE 2.0

WHAT I HAVE: enter image description here

WHAT I NEED: enter image description here

Upvotes: 0

Views: 61

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Should be 

UPDATE `xxx.xxx.gift_test_1`
SET PaymentLines = ARRAY(
    SELECT AS STRUCT * 
      REPLACE(IF(PaymentMethod = 'gift_card', 'gift_card', PaymentType) AS PaymentType)
    FROM UNNEST(PaymentLines) AS pay
  )
WHERE true

Upvotes: 1

Related Questions