Forum Discussion

Jerome22's avatar
Jerome22
Icon for Resolver I rankResolver I
1 year ago
Solved

updating records using copy job?

Hi,   when Data copy will support updates and not just adding rows? I have a lot of tables which can have updated records, so I want to update my destination not just adding new rows.   thanks.
data engineering
  • Jerome22's avatar
    Jerome22
    1 year ago

    ok... unfortunately it's not supporting deletes.

    and the update appear to update all the rows matching the ID without option to select a hash or timestamp column.

     

    well... I'll still use SQL statements for now. still faster with more control than relying on external tools like the copy job.

     

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
1 year ago

Copy jobs in Data Factory means there is no need for updates. Overwrite where ever possible ( Data Lake and Data Lakehouse )  ( Apache Spark and Delta Lake ) or ( Merge in Microsoft Fabric or 

 

Incremental Refresh with below code ( Append in Microsoft Fabric )

 

### Variables
LakehouseName = "Demo"
TableName = "DimTables"
ColName = "Surrogate_Key_Demo"
NumberOfRowsToRemove = "10"


### Remove Old rows
Reload = spark.sql("SELECT Max({0})-{1} as ReLoadValue FROM {2}.{3}".format(ColName,NumberOfRowsToRemove,LakehouseName,TableName)).collect()
Reload = Reload[0].ReLoadValue
display(Reload)

## ColName should be integer at all times to work (Incremental Refresh)
 
spark.sql("Delete from {0}.{1} where {2} > {3}".format(LakehouseName, TableName, ColName, Reload))
 
so it means you should always use Notebooks. ( Concept of Data Lake and Data Lakehouse)