SO question 60246807

queries.sql

-- create t1
CREATE TABLE
  overwrite.t1 (sales INT64,
    sdate DATE)
PARTITION BY
  sdate;
  
-- create t2
CREATE TABLE
  overwrite.t2 (sales INT64,
    pdate DATE)
PARTITION BY
  pdate;

-- populate t1
INSERT overwrite.t1 (sales, sdate) 
VALUES (1,DATE('2020-01-01')),
       (2,DATE('2020-01-02')),
       (3,DATE('2020-01-03'));

-- populate t2
INSERT overwrite.t2 (sales, pdate) 
VALUES (20,DATE('2020-01-02')),
       (30,DATE('2020-01-03')),
       (40,DATE('2020-01-04'));

-- delete target partitions
DELETE
  overwrite.t2
WHERE
  pdate BETWEEN "2020-01-01" and "2020-01-02";

-- update target partitions from source
INSERT overwrite.t2 (sales, pdate) 
  SELECT sales, sdate AS pdate
  FROM overwrite.t1
  WHERE sdate BETWEEN "2020-01-01" and "2020-01-02";

-- merge example (consider what will happen with more than one row per partition)
-- MERGE overwrite.t2
-- USING overwrite.t1
-- ON t2.pdate = t1.sdate
-- AND t1.sdate BETWEEN "2020-01-01" and "2020-01-02"
-- WHEN MATCHED THEN
--   UPDATE SET t2.sales = t1.sales
-- WHEN NOT MATCHED THEN
--   INSERT(sales, pdate) VALUES(t1.sales, t1.sdate)

original target table:

target table updated from source: