I have a Delta Table called Results, I have a list of operations that are required to be carried out on Results in sequence.

Given list of operations: ['op_1', 'op_2', 'op_3']

op_1 modifies the value column if the network column is within a certain IP range.

op_2 deletes the row column again if the value column is within a certain range.

op_3 modifies the grouping based on zone column.

As part of this process, I am required to modify another teams table called operation_log that is formatted in a certain way certain columns that tracks what operations were performed on which rows. They own this table, I cannot change this part.

The operations are performed on the delta table using a MERGE INTO statement and then afterwards the actions are tracked in operation_log

Currently I have the operation and then the log of that operation happening as two separate queries being executed. The problem I’m facing is that when the delete happens on the results, that row is no longer accessible from the table or any created views. So I must execute log sql first. Which is a problem if the log executes fine but the actual deletion sql fails. I have an action being recorded if no action occured.

I tried to combine everything into one sql statement to be executed. The delete statement example:

MERGE INTO results
USING  _vectors
    ON results.vector_id = _vectors.vector_id
WHEN MATCHED THEN
    DELETE
WHEN MATCHED THEN
    INSERT INTO operation_log (
        vector_id,
        ip_range,
        value,
        grouping,
        zone,
        action
        )
    VALUES (
        _vectors.vector_id,
        _vectors.ip_range,
        _vectors.value,
        _vectors.grouping,
        _vectors.zone,
        "DELETE"
    )

The only thing that is unfortunately a hard requirement is having this persnickety operation_log.

Is doing updates and inserts to separate tables possible in one query?