Bdtjtk

I have a huge table with 2m+ rows.
The structure is like that:

ThingName (STRING),

Date (DATE),

Value (INT64)

Sometimes Value is null and I need to fix it by setting it with NOT NULL Value of closest to it by Date row corresponding to ThingName...

And I am totally not SQL guy.

I tried to describe my task with this query (and simplified it a lot by using only previous dates (but actually I need to check future dates too)):

update my_tbl as SDP

set SDP.Value = (select SDPI.Value

    from my_tbl as SDPI

    where SDPI.Date < SDP.Date

    and SDP.ThingName = SDPI.ThingName

    and SDPI.Value is not null

    order by SDPI.Date desc limit 1)

where SDP.Value is null;

There I try to set updating row Value with one that I select from same table for same ThingName and with limit 1 I leave only single result.

But query editor tell me this:
Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

Actually, I am not sure at all that my task can be solved just with query.

So, can anyone help me? If this is impossible, then tell me this, if it possible, tell me what SQL constructions may help me.

Below is for BigQuery Standard SQL

In many (if not most) cases you don't want to update your table (as it incur extra cost and limitations associated with DML statements) but rather can adjust 'missing' values in-query - like in below example:

#standardSQL

SELECT 

  ThingName, 

  date, 

  IFNULL(value, 

    LAST_VALUE(value IGNORE NULLS) 

    OVER(PARTITION BY thingname ORDER BY date)

  ) AS value

FROM `project.dataset.my_tbl`

If for some reason you actually need to update the table - above statement will not help as DML's UPDATE does not allow use of analytic functions, so you need to use another approach. For example as below one

#standardSQL

SELECT 

  t1.ThingName, t1.date, 

  ARRAY_AGG(t2.Value IGNORE NULLS ORDER BY t2.date DESC LIMIT 1)[OFFSET(0)] AS value

FROM `project.dataset.my_tbl` AS t1

LEFT JOIN `project.dataset.my_tbl` AS t2

ON t2.ThingName = t1.ThingName

AND t2.date <= t1.date

GROUP BY t1.ThingName, t1.date, t1.value

and now you can use it to update your table as in example below

#standardSQL

UPDATE `project.dataset.my_tbl` t

SET value = new_value

FROM (

  SELECT TO_JSON_STRING(t1) AS id, 

    ARRAY_AGG(t2.Value IGNORE NULLS ORDER BY t2.date DESC LIMIT 1)[OFFSET(0)] new_value

  FROM `project.dataset.my_tbl` AS t1

  LEFT JOIN `project.dataset.my_tbl` AS t2

  ON t2.ThingName = t1.ThingName

  AND t2.date <= t1.date 

  GROUP BY id

)

WHERE TO_JSON_STRING(t) = id   

In BigQuery, updates are rather rare. The logic you seem to want is:

select t.*,

       coalesce(value,

                lag(value ignore nulls) over (partition by thingname order by date)

               ) as value

from my_tbl;

I don't really see a reason to save this back in the table.