You can then delete data from your target table by running a job with the following format:
CREATE JOB my_merge_jobASMERGEINTO my_catalog.my_schema.target_table AStargetUSING (SELECT f1 AS field1, f2 AS field2, f3 AS field3, pk, to_deleteFROM my_catalog.my_schema.source_tableWHERE $timeBETWEEN execution_start_time() AND execution_end_time()) sourceON target.pk = source.pkWHENMATCHEDAND to_delete THENDELETEWHENMATCHEDTHENREPLACEWHENNOTMATCHEDTHENINSERT MAP_COLUMNS_BY_NAME EXCEPT to_delete;
This job first matches your data based on the columns provided in the ON condition. If the values match and the delete condition to_delete is met, Upsolver deletes the data from your target table.
If you omit the ON statement, there should be a natural join for all primary key and partition columns. In this example, the name of the primary key column pk in the target table matches the name of a column in the source table, so this query works even if the ON statement is omitted.
If the column names are different, you can rename the source table column in the SELECT statement:
CREATE JOB my_merge_jobASMERGEINTO my_catalog.my_schema.target_table AStargetUSING (SELECT f1 AS field1, f2 AS field2, f3 AS field3, primary_key AS pk, to_deleteFROM my_catalog.my_schema.source_tableWHERE $timeBETWEEN execution_start_time() AND execution_end_time()) sourceON target.pk = source.pkWHENMATCHEDAND to_delete THENDELETEWHENMATCHEDTHENREPLACEWHENNOTMATCHEDTHENINSERT MAP_COLUMNS_BY_NAME
Another option is to merge using (column, ...) values (expression, ...) instead:
CREATE JOB my_merge_jobASMERGEINTO my_catalog.my_schema.target_table AStargetUSING (SELECT f1 AS field1, f2 AS field2, f3 AS field3, primary_key, to_deleteFROM my_catalog.my_schema.source_tableWHERE $timeBETWEEN execution_start_time() AND execution_end_time()) sourceON target.pk = source.primary_keyWHENMATCHEDAND to_delete THENDELETEWHENMATCHEDTHENREPLACEWHENNOTMATCHEDTHENINSERT (col1, col2, col3, pk) VALUES (field1, field2, field3, primary_key)
By explicitly mapping the values in the primary_key column of the source table to the pk column of the target table, you can match your data using these two columns even though they do not share the same name.
Last updated
To delete data from your target table, create a transformation job that includes a statement. The job requires that your target table has at least one primary key to match rows between source and target.
Note that we've included the clause here, indicating that the specified column to_delete should be excluded and not inserted into the target table.
For the rest of the columns, the keyword merges data from the source table to the target table based on matching column names.