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!

[Ethan]

Sure, but it would be useful to view your current query to give a more precise direction. Generally speaking, in SQL, if you want to implement an UPDATE operation based on a selection, it would look something like this:

`sql
UPDATE your_table
SET page_location = ‘new value’
WHERE event_name = ‘page_view’ AND page_location LIKE ‘%specific pattern%’
`

Please replace ‘your_table’ with the name of your table, ‘new value’ with the new page_location value you want to set, and ‘%specific pattern%’ with the specific pattern you mentioned. This would set the ‘page_location’ to ‘new value’ for all records where ‘event_name’ is ‘page_view’ and ‘page_location’ contains the specific pattern.