I am referring to this link of databricks documentation. Here is a screenshot of the same
According to the documentation the UPDATE command should work when the target table doesn't have the column but it is present in source. I tried the same with the following code and it failed.
%sql
CREATE OR REPLACE TABLE edw_nprd_aen.bronze.test_table (
a INT
, b STRING
);
INSERT INTO edw_nprd_aen.bronze.test_table
VALUES (1, 'a'), (2, 'b'), (3, 'c');
SELECT *
FROM edw_nprd_aen.bronze.test_table
%sql
CREATE OR REPLACE TEMPORARY VIEW test_view (
a
, c
)
AS VALUES (1, 'e'), (2, NULL), (3, 'd');%sql
MERGE WITH SCHEMA EVOLUTION INTO edw_nprd_aen.bronze.test_table
USING test_view
ON test_table.a = test_view.a
WHEN MATCHED THEN UPDATE SET
test_table.c = test_view.c
#Output
[DELTA_MERGE_UNRESOLVED_EXPRESSION] Cannot resolve test_table.c in UPDATE clause given columns test_view.a, test_view.c. SQLSTATE: 42601
== SQL (line 1, position 1) ==
MERGE WITH SCHEMA EVOLUTION INTO edw_nprd_aen.bronze.test_table
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
USING test_view
^^^^^^^^^^^^^^^
ON test_table.a = test_view.a
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
WHEN MATCHED THEN UPDATE SET
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
test_table.c = test_view.c
^^^^^^^^^^^^^^^^^^^^^^^^^^^^I have run this on both serverless and all-purpose cluster.
@Dhruv-22 I tested this and when I do something like follows, it works:
%sql
MERGE WITH SCHEMA EVOLUTION INTO main.yash.test_table
USING test_view
ON test_table.a = test_view.a
WHEN MATCHED THEN
UPDATE SET c = test_view.c Instead of mentioning it as test_table.c, I just did c, and it worked.
