Reply To: Modifying GA4 Big Query SQL records using UPDATE statement and UNNEST

[Sophia]

Sure, I can help you with that. Here’s a good way to approach it by using an UPDATE-SET-FROM statement, which is pretty handy but isn’t explicitly listed in the Google documentation.

The basic pattern you’d use goes a little something like this:

`sql
update ds.targettable t
set t.targetfield = s.sourcefield
from (select keyfield, sourcefield
from ds.sourcetable
) s
where t.keyfield = s.keyfield
`
Now, like I said it’s super useful to be able to pull information from one table to update another table like this. For your specific situation, you’d probably need to tweak it a bit. I don’t have direct access to your GA events table, so I can only offer you a rough estimation, but hopefully this helps:

`sql
update project-name.analytics_299XXXXXX.events_* tgt
set page_location = src.page
from (SELECT event_name, key, _table_suffix (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = ‘page_view’ AND key = ‘page_location’) AS page
FROM project-name.analytics_299XXXXXX.events_*
WHERE _table_suffix BETWEEN ‘20220322’
AND FORMAT_DATE(‘%Y%m%d’,DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = ‘page_view’
and page LIKE ‘%login%’) src
where farm_fingerprint(concat(tgt.key, tgt.event_name) = farm_fingerprint(concat(src.key, src.event_name)
and src._table_suffix = tgt._table_suffix
and tgt.page LIKE ‘%login%’
`
Table suffix comparison might be a bit tricky though, you’ll need to experiment with that to get it right.

But above all, let me know if you have any questions or need any clarification, I’d be happy to assist. Good luck!