How perform Upsert/delete in Serverless SQL pool?

Question

I have table in serverless SQL pool backed by parquet file and I want to update records in it. Thanks

Answer 1

Hello @Abdul Rehman, thanks for reaching out on this forum.

Using MERGE to do INSERT and UPDATE operations on a table in a single statement

A common scenario is updating one or more columns in a table if a matching row exists. Or, inserting the data as a new row if a matching row doesn't exist. You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can do both tasks in a single statement.

MERGE (Transact-SQL)

The MERGE command in Azure Synapse Analytics allows customers to insert, update, or delete data from one table based on the differences discovered in another table. With the MERGE command, the previous multi-step process to synchronize two tables in a dedicated SQL pool is now streamlined into a single step, thus reducing the data operation cost and improving the user experience.

Here is an example of how to use the MERGE statement to perform an upsert operation:

MERGE INTO target_table AS T
USING source_table AS S
ON T.key_column = S.key_column
WHEN MATCHED THEN
    UPDATE SET T.column1 = S.column1, T.column2 = S.column2
WHEN NOT MATCHED THEN
    INSERT (key_column, column1, column2)
    VALUES (S.key_column, S.column1, S.column2);
In this example, target_table is the table that you want to update, and source_table is the table that contains the new data. The key_column is the column that is used to match the rows in the two tables1. The WHEN MATCHED clause updates the columns in the target table with the values from the source table, and the WHEN NOT MATCHED clause inserts new rows into the target table.

Here is an example of how to use the MERGE statement to perform a delete operation:

MERGE INTO target_table AS T
USING source_table AS S
ON T.key_column = S.key_column
WHEN MATCHED THEN DELETE;
In this example, the WHEN MATCHED clause deletes the rows in the target table that match the rows in the source table.