Update bigquery value based on partition by row number

user_14771838 0
Viewed 330

I have a table in which I have records on the wrong date. I want to update them to be the day before for "snapshot_date". I have written the query to select the values I want to update the date for, but I don't know how to write the update query to change it to the previous day.

See screenshot

enter image description here

Query to select problematic records

Select * FROM(
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Period, User_Struct) rn 
     FROM `XXX.YYY.TABLE` 
     where Snapshot_Date = '2021-10-04'
     order by Period, User_Struct, Num_Active_Users asc
) where rn = 1
google-bigquery sql-update sql
1 Answers

Using DATE_SUB you may get the previous day i.e.

SELECT DATE_SUB(cast('2021-10-04' as DATE), interval '1' day)

will give 2021-10-03.

You may try the following using Big Query Update Statement Syntax

UPDATE 
    `XXX.YYY.TABLE` t0
SET
    t0.Snapshot_Date = DATE_SUB(t2.Snapshot_Date, interval '1' day)
FROM (
    SELECT * FROM(
        SELECT 
            *,
            ROW_NUMBER() OVER(PARTITION BY Period, User_Struct) rn 
        FROM 
            `XXX.YYY.TABLE` 
        WHERE
             Snapshot_Date = '2021-10-04'
        ORDER BY -- recommend removing order by here and use recommendation below for row_number
             Period, User_Struct, Num_Active_Users asc
    ) t1 
    WHERE rn = 1
)  t2
WHERE
   t0.Snapshot_Date = t2.Snapshot_Date AND -- include other columns to match/join subquery with main table on

You should also specify how your rows should be ordered when using ROW_NUMBER eg

ROW_NUMBER() OVER (PARTITION BY Period, User_Struct ORDER BY Num_Active_Users asc)

if this generates the same/desired results.

Let me know if this works for you.

user_43071360
Related