Delete rows from Table, remove unpaid transaction

Question

What to do if you want to delete rows?

Use UNNEST -

Error: Cannot access field hitId on a value with type ARRAY< STRUCT< hitId STRING, isEntrance INT64, isExit INT64, ...>>

delete
FROM
xxxx.session_streaming_20161029
WHERE 
h.hitId = (
SELECT hitId
FROM xxxx.session_streaming_20161029, UNNEST(hits) as h
WHERE h.transaction.transactionId = '123456')

Elliott Brossard Elliott Brossard · Accepted Answer · 2016-11-03T13:49:29

If I understand correctly, I think that you actually want:

delete FROM xxxx.session_streaming_20161029
WHERE EXISTS (
  SELECT 1
  FROM xxxx.session_streaming_20161029,
    UNNEST(hits) as h
  WHERE h.transaction.transactionId = '123456');

The idea is to match all rows where there is some transaction ID with value '123456'.

Edit: If the goal is to remove entries from hits with this transaction ID, and not to remove entire rows where hits contains the transaction ID, you can use UPDATE:

UPDATE xxxx.session_streaming_20161029
SET hits =
  ARRAY(SELECT hit FROM UNNEST(hits)
        WHERE transaction.transactionId = '123456');

Delete rows from Table, remove unpaid transaction

1 Answers