Using LAG() on an UPDATE with CASE statements

I have a query on a Python Dashboard (using Plotly/Dash) that let users set some variables and update a table in BigQuery.

I’m having trouble with one query that has some CASE statements and using LAG().

I wrote the following query:

DECLARE cpi STRING DEFAULT "{cpi}";
UPDATE `table` SET
PAY_FT = CASE
WHEN RN = 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 1
WHEN RN = 1 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN 1
WHEN RN = 1 THEN 0
WHEN RN > 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 0.2 + (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
WHEN RN > 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 0.2 + (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
ELSE (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
END
WHERE DEPTH_M IS NOT NULL

But I got the following error: “Analytic function not allowed in UPDATE clause…”.

I researched about it and it’s caused by the LAG function, which uses de OVER clause.

I have a column named RN on my dataset, but it could use a row_number() function if it helps.

I don’t know what to do to fix it.

Can someone help? Thanks in advance!

Answer

Big Query allows a FROM clause in UPDATE statements…

• https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#update_statement

So, you can write a sub-query which reads from your table and uses LAG() in your CASE expression.

Then, use the UPDATE‘s WHERE clause to join that result set back on to the target table, using whatever the table’s primary key is.

UPDATE
  table t
SET
  PAF_FT = s.PAY_FT
FROM
(
  SELECT
    <primary_key>,
    CASE
      WHEN RN = 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 1
      WHEN RN = 1 AND cpi = "C" AND PAY_CLEAN = "PAY" THEN 1
      WHEN RN = 1 THEN 0
      WHEN RN > 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 0.2 + (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
      WHEN RN > 1 AND cpi = "S" AND PAY_SHALE_COR = "PAY" THEN 0.2 + (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
      ELSE (LAG(PAY_FT, 1, 0) OVER(ORDER BY DEPTH_M))
    END
      AS PAY_FT
  FROM
    table
  WHERE
    DEPTH_M IS NOT NULL
)
  s
WHERE
  t.<primary_key> = s.<primary_key>