Hi,
I have a delta table that needs updating with new data every 10 mins. The data that comes in has many fields but maybe only 1 of them is actually different from the target table.
As I need to track these changes, I don't want to update the whole row when most of the fields are the same.
I am using the SQL command below to do this, but getting the following error:
Subqueries are not supported in the UPDATE condition of MERGE operation (condition = exists(t.label, s.label)). org.apache.spark.sql.delta.DeltaErrorsBase.subqueryNotSupportedException(DeltaErrors.scala:1127)
Is this a PySpark/Fabric problem? Is there an alternative way to do this?
The following code reproduces the problem (output path and target table need changing):
In this example, only the 1st row would be updated and only the "label" column would be.
df1 = spark.createDataFrame(
[
(1, "foo", "static1"),
(2, "bar", "static2"),
],
["id", "label", "staticCol"]
)
df2 = spark.createDataFrame(
[
(1, "update", "static1"),
(2, "bar", "static2"),
],
["id", "label", "staticCol"]
)
df2.createOrReplaceTempView("streamView")
output_path = 'Tables/TestUpdate'
df1.write.mode('overwrite').format('delta').save(output_path)
%%sql
MERGE INTO Lakehouse_Dev.TestUpdate t
USING streamView s
ON t.label = s.label
WHEN MATCHED AND EXISTS
(
SELECT t.label, t.staticCol
EXCEPT
(SELECT s.label, s.staticCol)
)
THEN
UPDATE SET
t.label = s.label
t.staticCol= s.staticCol
WHEN NOT MATCHED BY TARGET
THEN
INSERT
(label, staticCol)
VALUES
(s.label, s.staticCol)
