SQL Merge not adding new columns to delta table

Benedetta — Fri, 14 Jul 2023 14:59:02 GMT

SQL Merge not adding new columns to delta table error is: cannot resolve Target AuditUpdateDate in UPDATE clause given columns in Source

this config is set: spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled",True)

We're using DBR 12.2 LTS (includes Apache Spark 3.3.2, Scala 2.12)

Per the documentation below seems like new columns should be added.

What am I doing wrong? Thanks!

Benedetta

SQL query below trying to add new column: AuditUpdateDate

MERGE INTO XYZ.table1 AS Target USING cleansedTV AS Source

ON Target.col1 = Source.col1 AND Target.col2 = Source.col2

WHEN MATCHED AND( Target.col3 <> Source.col3

AND Target.col4 <> Source.col4

AND Target.col5 <> Source.col5

)

THEN UPDATE SET

Target.col3 = Source.col3

, Target.col4 = Source.col4

, Target.col5 = Source.col5

,Target.AuditUpdateDate = Source.AuditUpdateDate ----> Trying to add new column here

WHEN NOT MATCHED THEN INSERT *

topic SQL Merge not adding new columns to delta table in Get Started Discussions