I am trying to use a replacewhere clause on delta tables using azure databricks. Here is the setup to recreate the issue:
CREATE TABLE mymaintable (dt DATE, name STRING, YN string) USING delta;
INSERT INTO mymaintable VALUES ('2024-03-01', 'N1', 'Y');
INSERT INTO mymaintable VALUES ('2024-03-01', 'N2', 'N');
INSERT INTO mymaintable VALUES ('2024-03-01', 'N3', 'Y');
INSERT INTO mymaintable VALUES ('2024-03-02', 'N1', 'N');
INSERT INTO mymaintable VALUES ('2024-03-02', 'N2', 'N');
INSERT INTO mymaintable VALUES ('2024-03-02', 'N3', 'N');
INSERT INTO mymaintable VALUES ('2024-03-03', 'N1', 'Y');
INSERT INTO mymaintable VALUES ('2024-03-03', 'N2', 'Y');
INSERT INTO mymaintable VALUES ('2024-03-03', 'N3', 'Y');
CREATE TABLE myincrementaltable (dt DATE, name STRING, YN string) USING delta;
INSERT INTO myincrementaltable VALUES ('2024-03-03', 'N1', 'X');
INSERT INTO myincrementaltable VALUES ('2024-03-03', 'N2', 'Z');
INSERT INTO myincrementaltable VALUES ('2024-03-04', 'Q1', 'X');
INSERT INTO myincrementaltable VALUES ('2024-03-04', 'Q2', 'Z');
this is the setup. Now I want to replacewhere the incremental table into the main table.
This works:
INSERT INTO mymaintable
REPLACE WHERE dt >= "2024-03-03"
TABLE myincrementaltable
but this does not:
INSERT INTO mymaintable
REPLACE WHERE dt >= (SELECT MAX(dt) from mymaintable)
TABLE myincrementaltable
it fails with the error:
AnalysisException: [TABLE_OR_VIEW_NOT_FOUND] The table or view `mymaintable` cannot be found. Verify the spelling and correctness of the schema and catalog.
Is there anyway to do this?
Thanks!
Can you try doing Variable (note use Runtime 14.1 or higher)?
DECLARE VARIABLE myvar DATE;
SET VAR myvar = (SELECT MAX(dt) from sandbox.mymaintable);
INSERT INTO sandbox.mymaintable
REPLACE WHERE dt >= myvar
TABLE sandbox.myincrementaltable
Below you can find link to docs: https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-variables