Databricks Community

Dhruv-22 · Friday

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.

iyashk-DB · Friday

@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.