Skip to content

SQL DELETE Statement

SQL Server MERGE Statement

Last Updated : 21 Jun, 2024

SQL Server MERGE statement combines INSERT, UPDATE, and DELETE operations into a single transaction.

MERGE in SQL Server

The MERGE statement in SQL provides a convenient way to perform INSERT, UPDATE, and DELETE operations together, which helps handle the large running databases. But unlike INSERT, UPDATE, and DELETE statements MERGE statement requires a source t,ble.

Now we know that the MERGE in SQL requires two tables: one is the target table on which we want to perform INSERT, UPDATE, and DELETE operations, and the other one is the source table which contains the new modified and correct data for the target table and is compared with the actual target table to modify it.

Syntax

The MERGE statement syntax is:

MERGE INTO target_table
USING source_table
ON merge_condition
WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, …
WHEN NOT MATCHED THEN INSERT (column1, column2, …) VALUES (value1, value2, …)
WHEN NOT MATCHED BY TARGET THEN DELETE;

SQL Server MERGE Statement Examples

Let’s look at an example of the MERGE statement in SQL Server. First, let’s create two tables PRODUCT_LIST (target table) and UPDATED_LIST (source table)

PRODUCT_LIST Table:

P_ID	P_NAME	P_PRICE
101	COFFEE	15.00
102	BISCUIT	20.00

UPDATED_LIST Table:

P_ID	P_NAME	P_PRICE
101	COFFEE	25.00
103	CHIPS	22.00

Query:

MERGE PRODUCT_LIST AS TARGET
USING UPDATED_LIST AS SOURCE
ON (TARGET.P_ID = SOURCE.P_ID)
WHEN MATCHED AND (TARGET.P_NAME <> SOURCE.P_NAME OR TARGET.P_PRICE <> SOURCE.P_PRICE)
THEN UPDATE SET TARGET.P_NAME = SOURCE.P_NAME, TARGET.P_PRICE = SOURCE.P_PRICE
WHEN NOT MATCHED BY TARGET
THEN INSERT (P_ID, P_NAME, P_PRICE)
     VALUES (SOURCE.P_ID, SOURCE.P_NAME, SOURCE.P_PRICE)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Output

P_ID	P_NAME	P_PRICE
101	COFFEE	25.00
103	CHIPS	22.00

References – MERGE – docs.microsoft MERGE – docs.oracle

SQL DELETE Statement

GeeksforGeeks

Improve

Article Tags :

Similar Reads

SQL MERGE Statement

SQL MERGE Statement combines INSERT, DELETE, and UPDATE statements into one single query. MERGE Statement in SQLMERGE statement in SQL is used to perform insert, update, and delete operations on a target table based on the results of JOIN with a source table. This allows users to synchronize two tab

Select Statement in MS SQL Server

The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani

Insert Statement in MS SQL Server

The SQL Server INSERT statement is a fundamental command used to add new rows of data to a table. Whether we are inserting specific values, utilizing default values or copying data from another table. In this guide, weâ€™ll explore various ways to use the Insert statement in MS SQL Server with the hel

SQL DELETE Statement

The SQL DELETE statement is one of the most commonly used commands in SQL (Structured Query Language). It allows you to remove one or more rows from the table depending on the situation. Unlike the DROP statement, which removes the entire table, the DELETE statement removes data (rows) from the tabl

SQL CASE Statement

The CASE statement in SQL is a versatile conditional expression that enables us to incorporate conditional logic directly within our queries. It allows you to return specific results based on certain conditions, enabling dynamic query outputs. Whether you need to create new columns, modify existing

SQL SELECT INTO Statement

The SELECT INTO statement in SQL is a powerful and efficient command that allow users to create a new table and populate it with data from an existing table or query result in a single step. This feature is especially useful for creating backups, extracting specific subsets of data, or preparing new

SQL SERVER | Conditional Statements

While loop: In SQL SERVER, while loop can be used in similar manner as any other programming language. A while loop will check the condition first and then execute the block of SQL Statements within it as long as the condition evaluates true. Syntax: WHILE condition BEGIN {...statements...} END; Par

SQL INSERT INTO SELECT Statement

In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with

Insert Into Select statement in MS SQL Server

The INSERT INTO SELECT statement in SQL Server is a versatile feature that enables you to efficiently copy data from one or more tables into another table. This functionality is essential for tasks such as data transfer, backup creation, and data merging. In this article, We will learn to Insert Int

SQL INSERT INTO Statement

The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Letâ€™s break down how this command works,